﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Drawing;
using System.Text;
using System.IO;

using PdfSharp;
using PdfSharp.Drawing;
using PdfSharp.Pdf;
using PdfSharp.Pdf.IO;
using CE.Mail;
using CE.Image;
using CE.Global;
using CE.Database;
using CE.CreditCard;
using CE.User;
using CE.PostCode;
using CE.CarInsurance;
using CE.Provider;
using CE.Security;
using CE.InputOut;

public partial class car_Insurance_etiqa_paymentmsg : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            lblMessage.Text = "";
            if (Request.Params["authresp"] != null && Request.Params["ordernumber"] != null && Request.Params["email"] != null)
            {
                string _authresp = Request.Params["authresp"].ToString();
                string _ordernumber = Request.Params["ordernumber"].ToString();
                string _authcode = "";
                string _authdt = "";
                string _cctref = "";
                string _amount = "";
                string _cemail = "";
                if (Request.Params["authcode"] != null)
                {
                    _authcode = Request.Params["authcode"].ToString();
                }

                if (Request.Params["authdt"] != null)
                {
                    _authdt = Request.Params["authdt"].ToString();
                }

                if (Request.Params["cctref"] != null)
                {
                    _cctref = Request.Params["cctref"].ToString();
                }

                if (Request.Params["amount"] != null)
                {
                    _amount = Request.Params["amount"].ToString();
                }

                _cemail = Request.Params["email"].ToString();
                string _email = Request.Params["email"].ToString();

                InsertEtiqaPaymentGateWayReply(_ordernumber, _authresp, _authcode, _authdt, _cctref, _amount, _cemail);
                ShowMessage(_authresp);
                //_authresp = "OK";
                if (_authresp == "OK")
                {
                    Int64 _newPolicyNumberId = 0;
                    string _newPolicyNumber = "";
                    string _customername = "";
                    _email += ";" + CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaCI_TO");
                    string _emailcc = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaCI_CC");
                    string _emailbcc = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaCI_BCC");

                    //ordernumber = "20101222002048000131";
                    //_ordernumber = "20101221002043000126";
                    Int64 _statusresult = 0;
                    EtiqaCarMoreDetail _etiqacarmoredetail = new EtiqaCarMoreDetail();
                    EtiqaCarMoreDetailDB _etiqacarmoredetaildb = new EtiqaCarMoreDetailDB();
                    _etiqacarmoredetail = _etiqacarmoredetaildb.GetCarMoreDetail(_ordernumber);

                    if (_etiqacarmoredetail != null)
                    {
                        string convertno = General.GetRefNumber(_etiqacarmoredetail.CECarId);
                        string _fileName = "E" + convertno + "_" + CE.Global.General.GetUTCDateTime().Day.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Month.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Year;
                        string _filePath = GetFilePath();
                        //_FilePath += _fileName + ".xls";

                        _etiqacarmoredetail.OrderNo = _ordernumber;
                        _etiqacarmoredetail.Info10 = _filePath + _fileName + ".xls";

                        _statusresult = _etiqacarmoredetaildb.UpdateStatus(_etiqacarmoredetail);

                        if (_statusresult > 0)
                        {
                            UpdateConfirmation(_etiqacarmoredetail);

                            string[] filelist = GeneratePDF(_ordernumber, out _customername, out _newPolicyNumberId, out _newPolicyNumber);
                            if (filelist.Length == 3 && _newPolicyNumberId > 0)
                            {
                                EtiqaCarPolicyNumberDB _policyNumberDb = new EtiqaCarPolicyNumberDB();
                                if (_policyNumberDb.UpdateIssued(_newPolicyNumberId, _ordernumber, _authcode, _authdt, _cctref, _cemail, _amount) > 0)
                                {
                                    if (filelist[0] != "" && filelist[1] != "" && filelist[2] != "")
                                    {

                                        if (SendEmailWithAttach(filelist, _email, _emailcc, _emailbcc, _customername, _ordernumber, _newPolicyNumber))
                                        {
                                            GenerateProposalFormToEtiqa(_etiqacarmoredetail.CECarId, _filePath, _fileName);
                                            //lblMessage.Text += "<br />Please check your email for your Certificate of Insurance.";
                                            Response.Redirect("paymentmsg.aspx?status=OK");

                                        }
                                        else
                                        {
                                            lblMessage.Text += "<br />Send email with attachments failed!";
                                        }
                                    }
                                    else
                                    {
                                        lblMessage.Text += "<br />PDF Files are not completed.";
                                    }
                                }
                                else
                                {
                                    lblMessage.Text += "Can't update policy number issued status. <br /> Policy Number Id =" + _newPolicyNumberId.ToString();
                                }
                            }
                            else
                            {
                                lblMessage.Text += "<br />PDF Files are completed or Policy Number did not generated.";
                            }
                        }
                        else
                        {
                            lblMessage.Text = "Can't update payment status.";
                        }
                    }
                    else
                    {

                        GetUserAccounts(_ordernumber);
                        Response.Redirect("paymentmsg.aspx?status=FAIL");
                    }
                }
            }
            else if (Request.Params["status"] != null)
            {
                if (Request.Params["status"] == "OK")
                {
                    ShowMessage("OK");
                    lblMessage.Text += "<br />Please check your email for your Certificate of Insurance.";
                }
                else if (Request.Params["status"] == "FAIL")
                {
                    ShowMessage("FAIL");
                }
                else
                {
                    Response.Redirect("~/");
                }
            }
            else
            {
                Response.Redirect("~/");
            }
        }
    }

    public bool SendEmailWithAttach(string[] filelist, string email, string emailCC, string emailBCC, string customername, string ordernumber, string policynumber)
    {
        bool _retValue = false;

        Uri myUri = new Uri(HttpContext.Current.Request.Url.ToString());

        string domainUrl = "http://" + myUri.Host;
        string _etiqaLogoPath = domainUrl;
        _etiqaLogoPath += CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaLogoPath");

        string mailBody = "<br>Dear " + customername.ToUpper() + ",<br><br>";
        mailBody += "<table><tr><td><strong>Policy Number</strong></td><td>:</td><td>" + policynumber + "</td></tr>";
        mailBody += "<tr><td><strong>Payment reference</strong></td><td>:</td><td>" + ordernumber + "</td></tr></table>";
        mailBody += "<br/><br/>Thank you for insuring with Etiqa Insurance Pte Ltd.";//Etiqa Berhad
        mailBody += "<br/><br/>We are pleased to enclose the policy documents as attached for your kind attention.";
        mailBody += "<br/><br />We confirm coverage effective from the insurance commencement date as specified in ";
        mailBody += "the policy documents. If the insurance is to commence today, the time of cover starts ";
        mailBody += "from the time of this email.";
        mailBody += "<br/><br/>If you have any queries, please do not hesitate to contact us at 6331 9222 or email to us at ";
        mailBody += "<a href='mailto:cxmotor@etiqa.com.sg'>cxmotor@etiqa.com.sg </a>";
        mailBody += "<br/><br/>Motor Underwriting Dept";
        mailBody += "<br/>Etiqa Insurance Pte Ltd";
        mailBody += "<br/><img src='" + _etiqaLogoPath + "' alt='Etiqa Insurance Logo' />";
        mailBody += "<br/><br/><br/><style='font-style:italic;'>This document is automatically generated. Please do not respond to this email.</style>";

        string mailTitle = "Confirmation of Coverage with Etiqa (Policy No. " + policynumber + ")";
        if (MyMailManager.SendEtiqaEmailByAttachedmentNew(customername, email, emailCC, emailBCC, mailTitle, mailBody, filelist))
        {
            _retValue = true;
        }
        else
        {
            _retValue = false;
        }

        return _retValue;
    }

    public void ShowMessage(string AUTH)
    {
        if (AUTH == "OK")
        {
            lblMessage.Text = "Transaction successful";
        }
        else
        {
            //lblMessage.Text = "Transaction Failed. <a href = '../etiqa_proposal_form.aspx'><b>Click Here</b></a> to try again!";
            lblMessage.Text = "Transaction Failed. <a href = '../../User/ViewResultHistory.aspx'><b>Click Here</b></a> to try again!";
        }
        //else if (AUTH == "NA")
        //{
        //    lblMessage.Text = "All other host errors";
        //}
        //else if (AUTH == "IC")
        //{
        //    lblMessage.Text = "Expired card, or stolen, lost or invalid card from bank responses";
        //}
        //else if (AUTH == "RE")
        //{
        //    lblMessage.Text = "Referral";
        //}
        //else if (AUTH == "3D")
        //{
        //    lblMessage.Text = "Problem with Visa 3D was encountered such as invalid password by the customer";
        //}
        //else if (AUTH == "it")
        //{
        //    lblMessage.Text = "Fraud transaction – duplicate record with different details e.g. same OrderNumber but different amount.";
        //}
        //else if (AUTH == "TO")
        //{
        //    lblMessage.Text = "Timeout or host not available/couldn’t connect/or Host is busy doing a settlement and is not available";
        //}
        //else if (AUTH == "IT")
        //{
        //    lblMessage.Text = "Bad Merchant/Merchant invalid/invalid transaction (e.g. Amt equals to 0)";
        //}
        //else if (AUTH == "ic")
        //{
        //    lblMessage.Text = "Bad card format – card not in Bin Range for that particular merchant’s terminal.";
        //}
        //else if (AUTH == "L5" || AUTH == "L6")
        //{
        //    lblMessage.Text = "Velocity checks failed on amounts allowed for that card";
        //}
        //else if (AUTH == "L4")
        //{
        //    lblMessage.Text = "Too many transactions for that card";
        //}
        //else if (AUTH == "L2" || AUTH == "L3")
        //{
        //    lblMessage.Text = "Velocity checks failed on amounts allowed for that Merchant";
        //}
        //else if (AUTH == "L1")
        //{
        //    lblMessage.Text = "Amount too large";
        //}
    }

    public void UpdateConfirmation(EtiqaCarMoreDetail _etiqacarmoredetail)
    {

        UserConfirmation _userconfirm = new UserConfirmation();
        UserConfirmationDB _userconfirmdb = new UserConfirmationDB();
        _userconfirm.ConfirmationType = "CI";
        _userconfirm.CurrentStep = 4;
        //_userconfirm.UserResultId = Convert.ToInt64(Session[General.CarResultId].ToString());
        _userconfirm.UserSelectedId = 0;//Convert.ToInt64(Session[General.CarResultId].ToString());
        _userconfirm.FirstStepId = _etiqacarmoredetail.CECarId;//Convert.ToInt64(Session[General.CarId].ToString());
        _userconfirm.RefCEUserId = _etiqacarmoredetail.RefCEUserId;//Convert.ToInt64(Session[General.UserId].ToString());
        _userconfirm.IsConfirm = true;
        _userconfirm.ResultStatus = "Complete";
        _userconfirm.IsComplete = true;
        Int64 _result = _userconfirmdb.Insert(_userconfirm);

        //Account _account = new Account();
        //AccountDB _accountdb = new AccountDB();
        //_account = _accountdb.GetAccount(_etiqacarmoredetail.RefCEUserId);

        //if (_account != null)
        //{
        //    GetUserInformation(_account);
        //}

        //_account = null;
        //_accountdb = null;




        //General.ClearAllSession(this.Page, "ALL");
        //if ((_result > 0) && (_statusresult > 0))
        //{
        //    string _provideid = Encode(Session[General.ProviderCompanyId].ToString());
        //    General.ClearAllSession(this.Page, "ALL");
        //    Response.Redirect("UserMessage.aspx?pcid=" + _provideid);
        //}
        //else
        //{
        //    lblMessage.Text = "Can't update confirmation status.";
        //}
    }

    private void GetUserAccounts(string ORDERNO)
    {
        Int64 _UserId = 0;
        MyDatabase _db = new MyDatabase();
        string strSql = "";
        strSql = @"SELECT RefCEUserId FROM tblCarMoreDetail_Etiqa WHERE OrderNo = " + ORDERNO;
        DataTable _dtResult = new DataTable();
        _UserId = Convert.ToInt64(_db.ExecuteScalarSqlStatement(strSql));
        Account _account = new Account();
        AccountDB _accountdb = new AccountDB();
        _account = _accountdb.GetAccount(_UserId);

        if (_account != null)
        {
            GetUserInformation(_account);
        }

        _account = null;
        _accountdb = null;
    }

    public string GetPremiumDetails(string ORDERNO)
    {
        string _premium, _totaldue, _premiumgst, _premiumdue, _annualpremium, _ncdproduct, _ncdpremium, _ncd, _basicanlpremium, _nameddr, _unnameddr, _productpercent, _ofdstatus, _ofdamount, _ofdpercent;
        _premium = ""; _totaldue = ""; _premiumgst = ""; _premiumdue = ""; _annualpremium = ""; _ncdproduct = ""; _ncdpremium = ""; _productpercent = ""; _ofdpercent = "";
        _ncd = ""; _basicanlpremium = ""; _nameddr = ""; _unnameddr = "0.00"; _ofdstatus = ""; _ofdamount = "0.00";

        string _awsamount, _drivercharge;
        _awsamount = ""; _drivercharge = "";

        DataSet _dsPremium = new DataSet();
        EtiqaCarMoreDetail _etiqacarmoredetail = new EtiqaCarMoreDetail();
        EtiqaCarMoreDetailDB _etiqacarmoredetaildb = new EtiqaCarMoreDetailDB();
        _dsPremium = _etiqacarmoredetaildb.GetPremiumDetails(ORDERNO);
        if (_dsPremium != null && _dsPremium.Tables[0].Rows.Count > 0)
        {
            _totaldue = _dsPremium.Tables[0].Rows[0]["TotalDue"].ToString();
            _premiumgst = _dsPremium.Tables[0].Rows[0]["PremiumGST"].ToString();
            _premiumdue = _dsPremium.Tables[0].Rows[0]["PremiumDue"].ToString();
            _annualpremium = _dsPremium.Tables[0].Rows[0]["AnnualPremium"].ToString();
            _ncdproduct = _dsPremium.Tables[0].Rows[0]["NCDProduct"].ToString();
            _ncdpremium = _dsPremium.Tables[0].Rows[0]["NCDPremium"].ToString();
            _ncd = _dsPremium.Tables[0].Rows[0]["NCD"].ToString();
            _basicanlpremium = _dsPremium.Tables[0].Rows[0]["BasicAnlPremium"].ToString();

            _nameddr = _dsPremium.Tables[0].Rows[0]["DriverExessAmt"].ToString();

            Double _dNameDriverAmt = Convert.ToDouble(_nameddr);
            _unnameddr = Convert.ToString(_dNameDriverAmt * 2);

            _productpercent = _dsPremium.Tables[0].Rows[0]["ProductPercent"].ToString();
            _ofdstatus = _dsPremium.Tables[0].Rows[0]["OFDStatus"].ToString();
            _ofdamount = _dsPremium.Tables[0].Rows[0]["OFDAmount"].ToString();
            _ofdpercent = _dsPremium.Tables[0].Rows[0]["OFDPercent"].ToString();

            _awsamount = _dsPremium.Tables[0].Rows[0]["AWSAmount"].ToString();
            _drivercharge = _dsPremium.Tables[0].Rows[0]["DriverCharge"].ToString();
        }
        if (_dsPremium != null && _dsPremium.Tables.Count > 1 && _dsPremium.Tables[1].Rows.Count > 0)
        {
            _unnameddr = Convert.ToSingle(_dsPremium.Tables[1].Rows[0]["ExcessAmount"].ToString()).ToString("f2");
        }
        _premium = _totaldue + "," + _premiumgst + "," + _premiumdue + "," + _annualpremium + "," + _ncdproduct + "," + _ncdpremium + "," 
            + _ncd + "," + _basicanlpremium + "," + _nameddr + "," + _unnameddr + "," + _productpercent + "," + _ofdstatus + ","
            + _ofdamount + "," + _ofdpercent + "," + _awsamount + "," + _drivercharge;
        return _premium;
    }

    private string GetCarDriverNameList(string OrderNumber)
    {
        string _carDriverNameList = "";
        Int64 _UserId = 0;
        Int64 _CarId = 0;

        CarDriverDB _cardriverdb = new CarDriverDB();

        MyDatabase _db = new MyDatabase();

        string strSql = "";
        strSql = @"SELECT CECarId, RefCEUserId FROM tblCarMoreDetail_Etiqa WHERE OrderNo = " + OrderNumber;
        DataTable _dtResult = new DataTable();
        _dtResult = _db.GetDataTable(strSql);

        if (_dtResult != null && _dtResult.Rows.Count > 0)
        {
            _CarId = Convert.ToInt64(_dtResult.Rows[0][0].ToString());
            _UserId = Convert.ToInt64(_dtResult.Rows[0][1].ToString());

            DataTable _driverList = _cardriverdb.GetDriverList(_CarId, _UserId);

            if (_driverList != null && _driverList.Rows.Count > 0)
            {
                for (int i = 0; i < _driverList.Rows.Count; i++)
                {
                    if (_driverList.Rows[i]["SurName"] != null && _driverList.Rows[i]["SurName"].ToString() != string.Empty)
                    {
                        _carDriverNameList += _driverList.Rows[i]["SurName"].ToString();
                    }

                    if (_driverList.Rows[i]["GivenName"] != null && _driverList.Rows[i]["GivenName"].ToString() != string.Empty)
                    {
                        _carDriverNameList += " " + _driverList.Rows[i]["GivenName"].ToString();
                    }

                    if ((i + 1) < _driverList.Rows.Count)
                        _carDriverNameList += ";";
                }
            }
        }
        return _carDriverNameList;
    }

    public string[] GeneratePDF(string ORDERNO, out string customername, out Int64 policyNumberId, out string policyNumber)
    {
        CE.CarInsurance.EtiqaCarInsurancePDFOutput _etiPdfOut = new CE.CarInsurance.EtiqaCarInsurancePDFOutput();
        string[] fileList = new string[3];
        string _premium = GetPremiumDetails(ORDERNO);
        string[] Premium = _premium.Split(',');
        string _cardriverList = GetCarDriverNameList(ORDERNO);
        string _AgentStatus = "No";
        string _cxcovertypeid = "0";

        DataSet _dsPolicy = new DataSet();
        EtiqaCarMoreDetail _etiqacarmoredetail = new EtiqaCarMoreDetail();
        EtiqaCarMoreDetailDB _etiqacarmoredetaildb = new EtiqaCarMoreDetailDB();
        EtiqaCarPolicyNumber _policynumber = new EtiqaCarPolicyNumber();
        EtiqaCarPolicyNumberDB _policynumberdb = new EtiqaCarPolicyNumberDB();
        _policynumber = _policynumberdb.GetNewPolicyNumber(30);
        policyNumberId = 0;
        policyNumber = "";
        customername = "";
        _dsPolicy = _etiqacarmoredetaildb.GetPolicyDetails(ORDERNO);


        if (_policynumber != null && _dsPolicy != null && _dsPolicy.Tables[0].Rows.Count > 0)
        {
            _AgentStatus = _dsPolicy.Tables[0].Rows[0]["AgentStatus"].ToString();
            _cxcovertypeid = _dsPolicy.Tables[0].Rows[0]["CXCoverTypeId"].ToString();

            _etiPdfOut.ProposalId = _dsPolicy.Tables[0].Rows[0]["CECarMoreDetailId"].ToString();
            _etiPdfOut.CustomerName = _dsPolicy.Tables[0].Rows[0]["Name"].ToString();
            customername = _etiPdfOut.CustomerName;
            _etiPdfOut.CustomerAddress1 = "#" + _dsPolicy.Tables[0].Rows[0]["UnitNo"].ToString() + " - " + _dsPolicy.Tables[0].Rows[0]["HomeNo"].ToString() + ", " + _dsPolicy.Tables[0].Rows[0]["Address"].ToString();
            _etiPdfOut.CustomerAddress2 = "Singapore - " + _dsPolicy.Tables[0].Rows[0]["PostalCode"].ToString();
            _etiPdfOut.PolicyNumber = _policynumber.PolicyNumber;
            policyNumberId = _policynumber.PolicyNumberId;
            policyNumber = _policynumber.PolicyNumber;
            _etiPdfOut.GSTRegNumber = "M90363266Y";
            _etiPdfOut.TransactionDate = CE.Global.General.GetUTCDateTime().ToShortDateString();

            _etiPdfOut.BranchTerritory = "SINGAPORE/ Singapore";

            _etiPdfOut.TotalAnnualPermium = _dsPolicy.Tables[0].Rows[0]["AnnualPremium"].ToString();
            _etiPdfOut.CardType = "";//_dsPolicy.Tables[0].Rows[0]["CardType"].ToString();
            _etiPdfOut.CardLastFourDigit = "";//_dsPolicy.Tables[0].Rows[0]["CreditCardNo"].ToString().Substring(8, 4);

            if (_AgentStatus == "Yes")
            {
                _etiPdfOut.Agency = "000597";
                _etiPdfOut.Account = "000597";
            }
            else if (_AgentStatus == "No")
            {
                _etiPdfOut.Agency = "000598";
                _etiPdfOut.Account = "000598";
            }      

            //if (Session[CE.Global.General.AgentId] != null)
            //{
            //    _etiPdfOut.Agency = "000597"; 
            //}
            //else
            //{
            //    _etiPdfOut.Agency = "000598"; 
            //}

            _etiPdfOut.ClassOfPolicy = "MOTOR PRIVATE CAR";            
            _etiPdfOut.IssuedOn = _dsPolicy.Tables[0].Rows[0]["StartDatewd"].ToString() + " in SINGAPORE";
            _etiPdfOut.ReplacePolicyNumber = "";
            _etiPdfOut.Client = "";
            _etiPdfOut.AcceptanceDate = _dsPolicy.Tables[0].Rows[0]["StartDatewd"].ToString();

            _etiPdfOut.PeriodInsuranceStart = _dsPolicy.Tables[0].Rows[0]["StartDatewd"].ToString();
            _etiPdfOut.PeriodInsuranceEnd = _dsPolicy.Tables[0].Rows[0]["EndDatewd"].ToString();

            _etiPdfOut.Occupation = _dsPolicy.Tables[0].Rows[0]["NofBusi"].ToString() + " - " + _dsPolicy.Tables[0].Rows[0]["Description"].ToString();

            //Double _dBasicPremiumAmount = Convert.ToDouble(Premium[7].ToString());
            //_etiPdfOut.BasicPremiumAmount = "S$ " + _dBasicPremiumAmount.ToString("#,000.00");
            _etiPdfOut.BasicPremiumAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[7].ToString()));

            _etiPdfOut.LessNCDPercent = Premium[6].ToString() + "%";

            //Double _dLessNCDAmount = Convert.ToDouble(Premium[5].ToString());
            //_etiPdfOut.LessNCDAmount = "S$ " + _dLessNCDAmount.ToString("#,000.00"); 
            _etiPdfOut.LessNCDAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[5].ToString()));

            _etiPdfOut.NCDProtectionPremiumPercent = Premium[10].ToString();

            _etiPdfOut.IsOFD = Premium[11].ToString() == "0" ? false : true;
            _etiPdfOut.LessOFDAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[12].ToString()));
            _etiPdfOut.OFDPercent = Premium[13].ToString();
            //Double _dNCDProtectionPremiumAmount = Convert.ToDouble(Premium[4].ToString());
            //_etiPdfOut.NCDProtectionPremiumAmount = "S$ " + _dNCDProtectionPremiumAmount.ToString("#,000.00");

            if (Premium[4].ToString() != "0.00" && Premium[4].ToString() != "")
            {
                if (Convert.ToDouble(Premium[4].ToString()) > 0)
                {
                    _etiPdfOut.IsNCDProtector = true;
                }
                else
                {
                    _etiPdfOut.IsNCDProtector = false;
                }
                _etiPdfOut.NCDProtectionPremiumAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[4].ToString()));

            }

            if (Premium[14].ToString() != "0.00" && Premium[14].ToString() != "")
            {
                _etiPdfOut.LessAWSAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[14].ToString()));
            }

            if (Premium[15].ToString() != "0.00" && Premium[15].ToString() != "")
            {
                _etiPdfOut.AdditionalCharge = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[15].ToString()));
            }

            //_etiPdfOut.TotalAnnualPermium = "S$ " + string.Format("{0:0,0.00}",Premium[3].ToString());
            if (Premium[3].ToString() != "" && Premium[3].ToString() != "0.00")
            {
                _etiPdfOut.TotalAnnualPermium = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[3].ToString()));
            }

            //_etiPdfOut.PremiumDue = "S$ " + Premium[2].ToString();
             if (Premium[2].ToString() != "" && Premium[2].ToString() != "0.00")
            {
            _etiPdfOut.PremiumDue = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[2].ToString()));
             }
            
            //_etiPdfOut.PremiumGST = "S$ " + Premium[1].ToString();
             if (Premium[1].ToString() != "" && Premium[1].ToString() != "0.00")
            {
            _etiPdfOut.PremiumGST = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[1].ToString()));
             }
             

            //double _dtotDue = Convert.ToDouble(Premium[0].ToString());
            //_etiPdfOut.TotalDueInvoice = _dtotDue.ToString("#,000.00"); //Double S$ for Tax Invoice
            //_etiPdfOut.TotalDueSchedule = "S$ " + _dtotDue.ToString("#,000.00");

            _etiPdfOut.TotalDueInvoice = string.Format("{0:0,0.00}", Convert.ToDouble(Premium[0].ToString()));
            _etiPdfOut.TotalDueSchedule = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[0].ToString()));

            _etiPdfOut.RegistrationNo = _dsPolicy.Tables[0].Rows[0]["VechicleNo"].ToString();

            _etiPdfOut.MakeModel = _dsPolicy.Tables[1].Rows[0]["Description"].ToString();
            _etiPdfOut.TypeOfCover = _dsPolicy.Tables[0].Rows[0]["PlanName"].ToString();
            _etiPdfOut.NoOfSeats = "";
            _etiPdfOut.BodyType = _dsPolicy.Tables[1].Rows[0]["Body"].ToString();
            _etiPdfOut.EngineNo = _dsPolicy.Tables[0].Rows[0]["EngineNo"].ToString();
            _etiPdfOut.Capacity = _dsPolicy.Tables[1].Rows[0]["CC"].ToString();
            _etiPdfOut.RegYear = _dsPolicy.Tables[0].Rows[0]["RegYear"].ToString();
            _etiPdfOut.ChassisNo = _dsPolicy.Tables[0].Rows[0]["ChassisNo"].ToString();
            _etiPdfOut.CertificateRef = " ";
            _etiPdfOut.DriverNameList = _cardriverList;
            _etiPdfOut.ExcessNameDriverAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[8].ToString()));

            if (_cxcovertypeid.Trim() == "2") //Panel
            {
                double dexcess = Convert.ToDouble(Premium[9].ToString()) * 2;
                _etiPdfOut.ExcessUnNameDriverAmount = "S$ " + string.Format("{0:0,0.00}", dexcess);
            }
            else
            {
                _etiPdfOut.ExcessUnNameDriverAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[9].ToString()));
            }

            if (_dsPolicy.Tables[2].Rows.Count > 0)
            {
                _etiPdfOut.HirePurchaseCompanyName = _dsPolicy.Tables[2].Rows[0]["HPCompanyName"].ToString();
            }
            else
            {
                _etiPdfOut.HirePurchaseCompanyName = "N/A";
            }
            _etiPdfOut.CertificateNumber = _policynumber.PolicyNumber;

            if (!_etiPdfOut.GenerateAllOuput(out fileList))
            {
                fileList[0] = "";
                fileList[1] = "";
                fileList[2] = "";
            }
        }
        else
        {
            fileList[0] = "";
            fileList[1] = "";
            fileList[2] = "";
            #region No Need
            //_etiPdfOut.ProposalId = "";
            //_etiPdfOut.CustomerName = "";
            //_etiPdfOut.CustomerAddress1 = "";
            //_etiPdfOut.CustomerAddress2 = "";
            //_etiPdfOut.PolicyNumber = "DPMP0000001";

            //_etiPdfOut.GSTRegNumber = "M90363266Y";
            //_etiPdfOut.TransactionDate = CE.Global.General.GetUTCDateTime().ToShortDateString();

            //_etiPdfOut.BranchTerritory = "SINGAPORE/ Singapore";

            //_etiPdfOut.TotalAnnualPermium = "";
            //_etiPdfOut.CardType = "";
            //_etiPdfOut.CardLastFourDigit = "";

            //_etiPdfOut.Agency = "001045";
            //_etiPdfOut.ClassOfPolicy = "MOTOR PRIVATE CAR";
            //_etiPdfOut.Account = "001045";
            //_etiPdfOut.IssuedOn = "";
            //_etiPdfOut.ReplacePolicyNumber = "DMAP095006946";
            //_etiPdfOut.Client = "";
            //_etiPdfOut.AcceptanceDate = "";

            //_etiPdfOut.PeriodInsuranceStart = "";
            //_etiPdfOut.PeriodInsuranceEnd = "";

            //_etiPdfOut.Occupation = "";
            //_etiPdfOut.BasicPremiumAmount = "S$" + Premium[7].ToString();
            //_etiPdfOut.LessNCDPercent = Premium[6].ToString() + "%";
            //_etiPdfOut.LessNCDAmount = "S$" + Premium[5].ToString();
            //_etiPdfOut.NCDProtectionPremiumPercent = Premium[10].ToString();
            //_etiPdfOut.NCDProtectionPremiumAmount = "S$" + Premium[4].ToString();
            //_etiPdfOut.TotalAnnualPermium = "S$" + Premium[3].ToString();
            //_etiPdfOut.PremiumDue = "S$" + Premium[2].ToString();
            //_etiPdfOut.PremiumGST = "S$" + Premium[1].ToString();
            //_etiPdfOut.TotalDue = "S$" + Premium[0].ToString();

            //_etiPdfOut.RegistrationNo = "";

            //_etiPdfOut.MakeModel = "";
            //_etiPdfOut.TypeOfCover = "";
            //_etiPdfOut.NoOfSeats = "";
            //_etiPdfOut.BodyType = "";
            //_etiPdfOut.EngineNo = "";
            //_etiPdfOut.Capacity = "";
            //_etiPdfOut.RegYear = "";
            //_etiPdfOut.ChassisNo = "";
            //_etiPdfOut.CertificateRef = "";
            //_etiPdfOut.ExcessNameDriverAmount = "";
            //_etiPdfOut.ExcessUnNameDriverAmount = "";
            //_etiPdfOut.HirePurchaseCompanyName = "";

            //_etiPdfOut.CertificateNumber = "DMAP10S004354";
            #endregion
        }

        return fileList;
    }

    private string EtiqaScheduleOutputSample()
    {
        string _insuredName, _startPeriod, _endPeriod, _address1, _address2, _occupation, _coverType, _issuedOn, _acceptanceDate;
        string _registrationNo, _makeModel, _typeOfCover, _bodyType, _engineNo, _capacity, _regYear, _chassisNo;
        _insuredName = ""; _startPeriod = ""; _endPeriod = ""; _address1 = ""; _address2 = ""; _occupation = ""; _coverType = "";
        _registrationNo = ""; _makeModel = ""; _typeOfCover = ""; _bodyType = ""; _engineNo = "";
        _capacity = ""; _regYear = ""; _chassisNo = ""; _issuedOn = ""; _acceptanceDate = "";
        string _orderNo = Request.Params["ordernumber"].ToString();

        string _premium = GetPremiumDetails(_orderNo);
        string[] Premium = _premium.Split(',');

        DataSet _dsPolicy = new DataSet();
        EtiqaCarMoreDetail _etiqacarmoredetail = new EtiqaCarMoreDetail();
        EtiqaCarMoreDetailDB _etiqacarmoredetaildb = new EtiqaCarMoreDetailDB();
        _dsPolicy = _etiqacarmoredetaildb.GetPolicyDetails(_orderNo);
        if (_dsPolicy != null && _dsPolicy.Tables[0].Rows.Count > 0)
        {
            _insuredName = _dsPolicy.Tables[0].Rows[0]["Name"].ToString();
            _startPeriod = _dsPolicy.Tables[0].Rows[0]["StartDate"].ToString();
            _issuedOn = _dsPolicy.Tables[0].Rows[0]["StartDate"].ToString();
            _acceptanceDate = _dsPolicy.Tables[0].Rows[0]["StartDate"].ToString();
            _endPeriod = _dsPolicy.Tables[0].Rows[0]["EndDate"].ToString();
            _address1 = "#" + _dsPolicy.Tables[0].Rows[0]["UnitNo"].ToString() + " - " + _dsPolicy.Tables[0].Rows[0]["HomeNo"].ToString() + ", " + _dsPolicy.Tables[0].Rows[0]["Address"].ToString();
            _address2 = "Singapore - " + _dsPolicy.Tables[0].Rows[0]["PostalCode"].ToString();
            _occupation = _dsPolicy.Tables[0].Rows[0]["NofBusi"].ToString() + " - " + _dsPolicy.Tables[0].Rows[0]["Description"].ToString();
            _coverType = _dsPolicy.Tables[0].Rows[0]["PlanName"].ToString();
            _typeOfCover = _dsPolicy.Tables[0].Rows[0]["PlanName"].ToString();
            //_premiumAmount = "S$" + _dsPolicy.Tables[0].Rows[0]["AnnualPremium"].ToString();
            //_totalDue = "S$" + _dsPolicy.Tables[0].Rows[0]["AnnualPremium"].ToString();
            _registrationNo = _dsPolicy.Tables[0].Rows[0]["VechicleNo"].ToString();
            _regYear = _dsPolicy.Tables[0].Rows[0]["RegYear"].ToString();
            _engineNo = _dsPolicy.Tables[0].Rows[0]["EngineNo"].ToString();
            _chassisNo = _dsPolicy.Tables[0].Rows[0]["ChassisNo"].ToString();
            //_totalAnnualPermium = "S$" + _dsPolicy.Tables[0].Rows[0]["AnnualPremium"].ToString();
        }
        if (_dsPolicy != null && _dsPolicy.Tables[1].Rows.Count > 0)
        {
            _makeModel = _dsPolicy.Tables[1].Rows[0]["Make"].ToString();
            _bodyType = _dsPolicy.Tables[1].Rows[0]["Description"].ToString();
            _capacity = _dsPolicy.Tables[1].Rows[0]["CC"].ToString();
        }
        // Create a new PDF document
        PdfDocument document = new PdfDocument();
        document.Info.Title = "Etiqa - SCHEDULE WRITING";

        // Create an empty page
        PdfPage page = document.AddPage();

        // Get an XGraphics object for drawing
        XGraphics gfx = XGraphics.FromPdfPage(page);

        //XPdfFontOptions options = new XPdfFontOptions(PdfFontEncoding.Unicode, PdfFontEmbedding.Always);

        // Create a font
        XFont font = new XFont("Verdana", 7, XFontStyle.Bold);
        XFont fontNormal = new XFont("Verdana", 7, XFontStyle.Regular);
        XColor _xcolor = new XColor();
        _xcolor.R = 0;
        _xcolor.G = 0;
        _xcolor.B = 0;
        System.Drawing.Rectangle _rect = new System.Drawing.Rectangle(20, 10, 500, 100);
        XPen _pen = new XPen(_xcolor);

        int curX = 50;
        int curY = 30;

        gfx.DrawImage(XImage.FromFile(Server.MapPath("~/images/pdf/header_etiqa_01.png")), new XRect(curX, curY, 520, 61));
        curY += 65;

        gfx.DrawString("ORIGINAL", font, XBrushes.Black, new XRect(curX, curY, 520, 20), XStringFormat.TopLeft);
        gfx.DrawString("THE SCHEDULE", font, XBrushes.Black, new XRect(curX, curY, 520, 20), XStringFormat.TopCenter);
        curY += 10;
        gfx.DrawLine(new XPen(_xcolor), curX, curY, 565, curY);
        curY += 15;
        string _agency = "001045";
        string _classOfPolicy = "MOTOR PRIVATE CAR";
        string _policyNumber = "DMAP105004354";
        string _account = "001045";
        //string _issuedOn = CE.Global.General.GetUTCDateTime().Day.ToString("00") + "/" + CE.Global.General.GetUTCDateTime().Month.ToString("00") + "/" + CE.Global.General.GetUTCDateTime().Year.ToString("0000") + " in SINGAPORE";
        string _replacePolicyNumber = "DMAP095006346";
        string _client = "";
        //string _acceptanceDate = CE.Global.General.GetUTCDateTime().Day.ToString("00") + "/" + CE.Global.General.GetUTCDateTime().Month.ToString("00") + "/" + CE.Global.General.GetUTCDateTime().Year.ToString("0000");

        gfx.DrawString("Agency", fontNormal, XBrushes.Black, new XRect(curX, curY, 100, 20), XStringFormat.TopLeft);
        gfx.DrawString(_agency, fontNormal, XBrushes.Black, new XRect(curX + 50, curY, 100, 20), XStringFormat.TopLeft);
        gfx.DrawString("Class of Policy", fontNormal, XBrushes.Black, new XRect(curX + 150, curY, 150, 20), XStringFormat.TopLeft);
        gfx.DrawString(_classOfPolicy, fontNormal, XBrushes.Black, new XRect(curX + 250, curY, 100, 20), XStringFormat.TopLeft);
        gfx.DrawString("Policy Number", fontNormal, XBrushes.Black, new XRect(curX + 350, curY, 150, 20), XStringFormat.TopLeft);
        gfx.DrawString(_policyNumber, fontNormal, XBrushes.Black, new XRect(curX + 450, curY, 100, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("Account", fontNormal, XBrushes.Black, new XRect(curX, curY, 100, 20), XStringFormat.TopLeft);
        gfx.DrawString(_account, fontNormal, XBrushes.Black, new XRect(curX + 50, curY, 100, 20), XStringFormat.TopLeft);
        gfx.DrawString("Issued On", fontNormal, XBrushes.Black, new XRect(curX + 150, curY, 150, 20), XStringFormat.TopLeft);
        gfx.DrawString(_issuedOn, fontNormal, XBrushes.Black, new XRect(curX + 250, curY, 100, 20), XStringFormat.TopLeft);
        gfx.DrawString("Replacing Policy No.", fontNormal, XBrushes.Black, new XRect(curX + 350, curY, 150, 20), XStringFormat.TopLeft);
        gfx.DrawString(_replacePolicyNumber, fontNormal, XBrushes.Black, new XRect(curX + 450, curY, 100, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("Client", fontNormal, XBrushes.Black, new XRect(curX, curY, 100, 20), XStringFormat.TopLeft);
        gfx.DrawString(_client, fontNormal, XBrushes.Black, new XRect(curX + 50, curY, 100, 20), XStringFormat.TopLeft);
        gfx.DrawString("Accpetance Date", fontNormal, XBrushes.Black, new XRect(curX + 150, curY, 150, 20), XStringFormat.TopLeft);
        gfx.DrawString(_acceptanceDate, fontNormal, XBrushes.Black, new XRect(curX + 250, curY, 100, 20), XStringFormat.TopLeft);
        curY += 20;
        gfx.DrawLine(new XPen(_xcolor), curX, curY, 565, curY);
        curY += 15;
        //string _startPeriod = CE.Global.General.GetUTCDateTime().Day.ToString("00") + "/" + CE.Global.General.GetUTCDateTime().Month.ToString("00") + "/" + CE.Global.General.GetUTCDateTime().Year.ToString("0000");
        //string _endPeriod = (CE.Global.General.GetUTCDateTime().Day - 1).ToString("00") + "/" + CE.Global.General.GetUTCDateTime().Month.ToString("00") + "/" + (CE.Global.General.GetUTCDateTime().Year + 1).ToString("0000");
        gfx.DrawString("Period of Insurance from", fontNormal, XBrushes.Black, new XRect(curX, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_startPeriod + " to " + _endPeriod + " , both dates inclusive", fontNormal, XBrushes.Black, new XRect(curX + 150, curY, 200, 20), XStringFormat.TopLeft);
        curY += 20;
        gfx.DrawLine(new XPen(_xcolor), curX, curY, 565, curY);
        curY += 25;
        //string _insuredName = "TAN SWEE MENG";
        //string _address1 = "4 SIN MING ROAD #03-01, SIN MING PLAZA";
        //string _address2 = "SINGAPORE 575584";
        gfx.DrawString("Insured's Name", fontNormal, XBrushes.Black, new XRect(curX, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_insuredName, fontNormal, XBrushes.Black, new XRect(curX + 150, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("Address", fontNormal, XBrushes.Black, new XRect(curX, curY, 200, 20), XStringFormat.TopLeft);

        gfx.DrawString(_address1, fontNormal, XBrushes.Black, new XRect(curX + 150, curY, 200, 20), XStringFormat.TopLeft);

        curY += 10;
        gfx.DrawString(_address2, fontNormal, XBrushes.Black, new XRect(curX + 150, curY, 200, 20), XStringFormat.TopLeft);
        curY += 20;
        gfx.DrawLine(new XPen(_xcolor), curX, curY, 565, curY);
        curY += 15;
        //string _occupation = "INDOOR - IT";
        gfx.DrawString("Business/Occupation", fontNormal, XBrushes.Black, new XRect(curX, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_occupation, fontNormal, XBrushes.Black, new XRect(curX + 150, curY, 200, 20), XStringFormat.TopLeft);
        curY += 20;
        gfx.DrawLine(new XPen(_xcolor), curX, curY, 565, curY);
        curY += 15;
        //string _coverType = "Basic Annual Premium";
        string _premiumAmount = "S$" + Premium[7].ToString();
        string _lessNCDPercent = Premium[6].ToString() + "%";
        string _lessNCDAmount = "S$" + Premium[5].ToString();
        string _ncdProtectionPremiumPercent = Premium[10].ToString();
        string _ncdProtectionPremiumAmount = "S$" + Premium[4].ToString();
        string _totalAnnualPermium = "S$" + Premium[3].ToString();
        string _premiumDue = "S$" + Premium[2].ToString();
        string _premiumGST = "S$" + Premium[1].ToString();
        string _totalDue = "S$" + Premium[0].ToString();

        gfx.DrawString("Premium", fontNormal, XBrushes.Black, new XRect(curX, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_coverType, fontNormal, XBrushes.Black, new XRect(curX + 150, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_premiumAmount, fontNormal, XBrushes.Black, new XRect(curX + 300, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("Less NCD", fontNormal, XBrushes.Black, new XRect(curX + 150, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_lessNCDPercent, fontNormal, XBrushes.Black, new XRect(curX + 250, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_lessNCDAmount, fontNormal, XBrushes.Black, new XRect(curX + 300, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        //Check NCD Protector
        if (Convert.ToInt32(Premium[4].ToString()) > 0)
        {
            gfx.DrawString("NCD Protection Premium", fontNormal, XBrushes.Black, new XRect(curX + 150, curY, 200, 20), XStringFormat.TopLeft);
            gfx.DrawString(_ncdProtectionPremiumPercent, fontNormal, XBrushes.Black, new XRect(curX + 250, curY, 200, 20), XStringFormat.TopLeft);
            gfx.DrawString(_ncdProtectionPremiumAmount, fontNormal, XBrushes.Black, new XRect(curX + 300, curY, 200, 20), XStringFormat.TopLeft);
            curY += 10;
        }
        gfx.DrawString("Total Annual Premium", fontNormal, XBrushes.Black, new XRect(curX + 150, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_totalAnnualPermium, fontNormal, XBrushes.Black, new XRect(curX + 300, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString("Premium Due", fontNormal, XBrushes.Black, new XRect(curX + 370, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_premiumDue, fontNormal, XBrushes.Black, new XRect(curX + 440, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("Premium GST", fontNormal, XBrushes.Black, new XRect(curX + 370, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_premiumGST, fontNormal, XBrushes.Black, new XRect(curX + 440, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("Total Due", fontNormal, XBrushes.Black, new XRect(curX + 370, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_totalDue, fontNormal, XBrushes.Black, new XRect(curX + 440, curY, 200, 20), XStringFormat.TopLeft);
        curY += 20;
        gfx.DrawLine(new XPen(_xcolor), curX, curY, 565, curY);
        curY += 15;
        gfx.DrawString("Risk No. 001", fontNormal, XBrushes.Black, new XRect(curX, curY, 200, 20), XStringFormat.TopLeft);

        int newX = curX;
        int _indent = 5;

        newX += _indent;
        gfx.DrawString(_classOfPolicy, fontNormal, XBrushes.Black, new XRect(curX + 150, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;

        gfx.DrawString("1.", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);

        //_indent += 5;
        newX += _indent;
        newX += _indent;

        //string _registrationNo = "SGU5007E";
        //string _makeModel = "MITSUBISHI LANCER 1.6M";
        //string _typeOfCover = "Comprehensive";
        string _noOfSeats = "5";
        //string _bodyType = "SALOON";
        //string _engineNo = "4G18HY9061";
        //string _capacity = "1584";
        //string _regYear = "2007";
        //string _chassisNo = "JMYSNCS3A7U006848";
        string _certificateRef = "";
        string _excessNameDriverAmount = "S$" + Premium[8].ToString();
        string _excessUnNameDriverAmount = "S$" + Premium[9].ToString();

        gfx.DrawString("Registration", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_registrationNo, fontNormal, XBrushes.Black, new XRect(curX + 150, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString("Make/Model", fontNormal, XBrushes.Black, new XRect(curX + 250, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_makeModel, fontNormal, XBrushes.Black, new XRect(curX + 300, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("Type of Cover", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_typeOfCover, fontNormal, XBrushes.Black, new XRect(curX + 150, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString("No. of Seats", fontNormal, XBrushes.Black, new XRect(curX + 250, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_noOfSeats, fontNormal, XBrushes.Black, new XRect(curX + 310, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString("Body Type", fontNormal, XBrushes.Black, new XRect(curX + 370, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_bodyType, fontNormal, XBrushes.Black, new XRect(curX + 430, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("Engine No.", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_engineNo, fontNormal, XBrushes.Black, new XRect(curX + 150, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString("Capacity cc's", fontNormal, XBrushes.Black, new XRect(curX + 250, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_capacity, fontNormal, XBrushes.Black, new XRect(curX + 310, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString("Year of Regn", fontNormal, XBrushes.Black, new XRect(curX + 370, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_regYear, fontNormal, XBrushes.Black, new XRect(curX + 430, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("Chassis No.", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_chassisNo, fontNormal, XBrushes.Black, new XRect(curX + 150, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("Certificate Ref.", fontNormal, XBrushes.Black, new XRect(curX + 400, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_certificateRef, fontNormal, XBrushes.Black, new XRect(curX + 460, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("Excess : Named Drivers", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_excessNameDriverAmount, fontNormal, XBrushes.Black, new XRect(curX + 200, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("Excess : UnNamed Drivers", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(_excessUnNameDriverAmount, fontNormal, XBrushes.Black, new XRect(curX + 200, curY, 200, 20), XStringFormat.TopLeft);

        curY += 10;
        gfx.DrawString("Named Drivers THE INSURED", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("The following clauses and endorsements apply to this risk", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        newX += 5;
        gfx.DrawString("NO CLAIM DISCOUNT", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);

        curY += 10;
        if (Convert.ToInt32(Premium[4].ToString()) > 0)
        {
            gfx.DrawString("NCD PROTECTION PREMIUM", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
            curY += 10;
        }
        gfx.DrawString("HIRE PURCHASE", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString("DBS BANK LTD", fontNormal, XBrushes.Black, new XRect(curX + 200, curY, 200, 20), XStringFormat.TopLeft);
        string filename = "~/TestPdfCsharp/" + "Etiqa_Scheduletempfile2.pdf";
        curY += 10;
        gfx.DrawString("ENDORSEMENT NO. MDP, MAP, M35", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("FLOOD &/OR OTHER CONVULSION OF NATURE AND STRIKE, RIOT & CIVIL COMMOTION", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("LEGAL LIABILITY OF PASSENGERS FOR ACTS OF NEGLIGENCE", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("PASSENGER LIABILITY ", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("FREE WINDSCREEN COVER, ADDITIONAL PREMIUM WILL BE CHARGED FOR REINSTATEMENT OF WINDSCREEN", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("SETTLEMENT BASED ON MARKET VALUE AT TIME OF LOSS", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("CONTRACTS (RIGHTS OF THIRD PARTIES) ACT 2001", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);

        curY += 15;
        gfx.DrawString("IT CLARIFICATION CLAUSE", font, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);

        curY += 10;
        gfx.DrawString("TERRORISM EXCLUSION ENDORSEMENT", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);


        curY += 10;
        gfx.DrawString("Limits of liability applicable:", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);


        curY += 10;
        gfx.DrawString("Item No. 2.3.1	Protection and removal after damage or loss", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(": $200", fontNormal, XBrushes.Black, new XRect(newX + 250, curY, 200, 20), XStringFormat.TopLeft);


        curY += 10;
        gfx.DrawString("Item No. 2.3.2	Authority to repair", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(": $300", fontNormal, XBrushes.Black, new XRect(newX + 250, curY, 200, 20), XStringFormat.TopLeft);

        curY += 10;
        gfx.DrawString("Item No. 3.1.1 (a) Legal liability for death or bodily injury", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(": Unlimited", fontNormal, XBrushes.Black, new XRect(newX + 250, curY, 200, 20), XStringFormat.TopLeft);

        curY += 10;
        gfx.DrawString("Item No. 3.1.1 (b) Legal liability for loss/damage to property", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(": $5m", fontNormal, XBrushes.Black, new XRect(newX + 250, curY, 200, 20), XStringFormat.TopLeft);

        curY += 10;
        gfx.DrawString("Item No. 3.1.2 	Legal representative and services", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(": $2,000", fontNormal, XBrushes.Black, new XRect(newX + 250, curY, 200, 20), XStringFormat.TopLeft);

        curY += 10;
        gfx.DrawString("Item No. 4.1	Medical expenses", fontNormal, XBrushes.Black, new XRect(newX, curY, 200, 20), XStringFormat.TopLeft);
        gfx.DrawString(": $200", fontNormal, XBrushes.Black, new XRect(newX + 250, curY, 200, 20), XStringFormat.TopLeft);
        curY += 20;
        gfx.DrawLine(new XPen(_xcolor), curX, curY, 565, curY);
        curY += 5;
        gfx.DrawString("The following clauses and endorsement apply to this policy", fontNormal, XBrushes.Black, new XRect(curX, curY, 200, 20), XStringFormat.TopLeft);
        curY += 40;
        gfx.DrawString("Continued on page 2", fontNormal, XBrushes.Black, new XRect(curX + 410, curY, 200, 20), XStringFormat.TopLeft);

        //Etiqa_Schedule_writings_New.pdf

        PdfDocument inputDocument = PdfReader.Open(Server.MapPath("~/images/pdf/Etiqa_Schedule_writings_New.pdf"), PdfDocumentOpenMode.Import);

        document.AddPage(inputDocument.Pages[0]);
        document.AddPage(inputDocument.Pages[1]);
        //document.AddPage(inputDocument.Pages[2]);
        //document.Save(filename);
        // ...and start a viewer.
        //Response.Redirect(filename);
        System.IO.MemoryStream stream = new System.IO.MemoryStream();
        document.Save(Server.MapPath(filename));
        stream.Close();
        document.Close();
        //Response.Redirect(filename);
        return filename;
    }

    private string EtiqaCertificateOutputSample()
    {
        string _vehicleNumber, _policyHolderName, _effCommencementDate;
        _vehicleNumber = ""; _policyHolderName = ""; _effCommencementDate = "";
        DateTime _dtNextYear = CE.Global.General.GetUTCDateTime();
        string _orderNo = Request.Params["ordernumber"].ToString();

        string _premium = GetPremiumDetails(_orderNo);
        string[] Premium = _premium.Split(',');

        DataSet _dsPolicy = new DataSet();
        EtiqaCarMoreDetail _etiqacarmoredetail = new EtiqaCarMoreDetail();
        EtiqaCarMoreDetailDB _etiqacarmoredetaildb = new EtiqaCarMoreDetailDB();
        _dsPolicy = _etiqacarmoredetaildb.GetPolicyDetails(_orderNo);
        if (_dsPolicy != null && _dsPolicy.Tables[0].Rows.Count > 0)
        {
            _vehicleNumber = _dsPolicy.Tables[0].Rows[0]["VechicleNo"].ToString();
            _policyHolderName = _dsPolicy.Tables[0].Rows[0]["Name"].ToString();
            DateTime policydate = Convert.ToDateTime(_dsPolicy.Tables[0].Rows[0]["StartDate"].ToString());
            _effCommencementDate = policydate.Day.ToString("00") + " " + policydate.Month.ToString("MMM") + " " + policydate.Year.ToString("00");
            _dtNextYear = Convert.ToDateTime(_dsPolicy.Tables[0].Rows[0]["EndDate"].ToString());
        }
        // Create a new PDF document
        PdfDocument document = new PdfDocument();
        document.Info.Title = "Etiqa - Certificate Of Insurance";

        // Create an empty page
        PdfPage page = document.AddPage();

        // Get an XGraphics object for drawing
        XGraphics gfx = XGraphics.FromPdfPage(page);

        //XPdfFontOptions options = new XPdfFontOptions(PdfFontEncoding.Unicode, PdfFontEmbedding.Always);

        // Create a font
        XFont font = new XFont("Verdana", 10, XFontStyle.Bold);

        XColor _xcolor = new XColor();
        _xcolor.R = 0;
        _xcolor.G = 0;
        _xcolor.B = 0;
        System.Drawing.Rectangle _rect = new System.Drawing.Rectangle(20, 10, 500, 100);
        XPen _pen = new XPen(_xcolor);

        int curX = 40;
        int curY = 50;

        gfx.DrawImage(XImage.FromFile(Server.MapPath("~/images/pdf/etiqa.jpg")), new XRect((page.Width / 2) - 60, curY, 120, 61));
        curY += 65;

        gfx.DrawString("CERTIFICATE OF INSURANCE", font, XBrushes.Black, new XRect(curX, curY, 50, 15), XStringFormat.TopLeft);
        curY += 15;
        XFont _fontNormal = new XFont("Verdana", 7, XFontStyle.Regular);
        gfx.DrawString("MOTOR VEHICLES (THIRD-PARTY RISKS AND COMPENSATION) ACT (CHAPTER 189). MOTOR VEHICLES (THIRD -PARTY RISKS AND COMPENSATION)", _fontNormal, XBrushes.Black, new XRect(curX, curY, 500, 15), XStringFormat.TopLeft);
        curY += 8;
        gfx.DrawString("RULES, 1960. ROAD TRANSPORT ACT, 1987 (MALAYSIA) . MOTOR VEHICLES (THIRD-PARTY RISKS) RULES, 1959 (MALAYSIA).", _fontNormal, XBrushes.Black, new XRect(curX, curY, 510, 15), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawRoundedRectangle(new XPen(_xcolor), new XRect(curX, curY, 535, 450), new Size(10, 10));

        curX += 8;
        curY += 15;
        int newX = curX + 150;
        int lineSeparator = 15;

        string _certNumber = "DMAP10S004354";
        gfx.DrawString("CERTIFICATE No.", _fontNormal, XBrushes.Black, new XRect(curX, curY, 500, 15), XStringFormat.TopLeft);
        gfx.DrawString(_certNumber, _fontNormal, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);
        curY += 20;
        curX -= 8;
        gfx.DrawLine(new XPen(_xcolor), curX, curY, 575, curY);

        curX += 8;
        curY += 20;

        //string _vehicleNumber = "SGU5007E";

        gfx.DrawString("1.  Index Mark and Registration", _fontNormal, XBrushes.Black, new XRect(curX, curY, 500, 15), XStringFormat.TopLeft);
        gfx.DrawString(_vehicleNumber, _fontNormal, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("     Number of Vehicle", _fontNormal, XBrushes.Black, new XRect(curX, curY, 500, 15), XStringFormat.TopLeft);
        curY += lineSeparator;

        //string _policyHolderName = "TAN SWEE MENG";

        gfx.DrawString("2.  Name of Policyholder", _fontNormal, XBrushes.Black, new XRect(curX, curY, 500, 15), XStringFormat.TopLeft);
        gfx.DrawString(_policyHolderName, _fontNormal, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);
        curY += lineSeparator;

        newX += 50;
        //string _effCommencementDate = CE.Global.General.GetUTCDateTime().Day.ToString("00") + " " + CE.Global.General.GetUTCDateTime().ToString("MMM") + " " + CE.Global.General.GetUTCDateTime().Year.ToString("00");

        gfx.DrawString("3.  Effective Date of Commencement of Insurance", _fontNormal, XBrushes.Black, new XRect(curX, curY, 500, 15), XStringFormat.TopLeft);
        gfx.DrawString(_effCommencementDate, _fontNormal, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);

        newX -= 50;
        curX += 300;
        newX += 250;
        string _excessNameDriversAmount = "S$" + Premium[8].ToString();
        gfx.DrawString("Excess : Named Drivers", _fontNormal, XBrushes.Black, new XRect(curX, curY, 500, 15), XStringFormat.TopLeft);
        gfx.DrawString(_excessNameDriversAmount, _fontNormal, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);

        curX -= 300;
        newX -= 250;
        curY += 10;
        gfx.DrawString("     for the purposes of the Act", _fontNormal, XBrushes.Black, new XRect(curX, curY, 500, 15), XStringFormat.TopLeft);

        curX += 300;
        newX += 250;
        string _excessUnNameDriversAmount = "S$" + Premium[9].ToString();
        gfx.DrawString("Excess : Unnamed Drivers", _fontNormal, XBrushes.Black, new XRect(curX, curY, 500, 15), XStringFormat.TopLeft);
        gfx.DrawString(_excessUnNameDriversAmount, _fontNormal, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);

        curX -= 300;
        newX -= 250;
        newX += 50;
        curY += lineSeparator;
        //DateTime _dtNextYear = new DateTime(CE.Global.General.GetUTCDateTime().Year + 1, CE.Global.General.GetUTCDateTime().Month, CE.Global.General.GetUTCDateTime().Day - 1);
        string _expiryDate = _dtNextYear.Day.ToString("00") + " " + _dtNextYear.ToString("MMM") + " " + _dtNextYear.Year.ToString("00");


        gfx.DrawString("4.  Date of Expiry of Insurance", _fontNormal, XBrushes.Black, new XRect(curX, curY, 500, 15), XStringFormat.TopLeft);
        gfx.DrawString(_expiryDate, _fontNormal, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);
        newX -= 50;
        newX -= 130;
        curY += lineSeparator;

        XFont _courierFont = new XFont("Courier", 6.2, XFontStyle.Regular);
        gfx.DrawString("5.  Persons or Classes of Persons entitled to drive", _fontNormal, XBrushes.Black, new XRect(curX, curY, 500, 15), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("(A)  THE POLICYHOLDER.", _courierFont, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("       THE POLICYHOLDER MAY ALSO DRIVE A MOTOR CAR NOT BELONGING TO HIM OR HIRED (UNDER A HIRE PURCHASE", _courierFont, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("       AGREEMENT OR OTHERWISE) TO HIM OR HIS EMPLOYER OR HIS PARTNER.", _courierFont, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("(B)  ANY OTHER PERSON WHO IS DRIVING ON THE POLICYHOLDER'S ORDER OR WITH HIS PERMISSION.", _courierFont, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);
        curY += 50;
        gfx.DrawString("THE INSURED", _courierFont, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);
        newX += 20;
        curY += lineSeparator;
        gfx.DrawString("Provided that the person driving is permitted in accordance with the licensing or other laws or regulations to", _fontNormal, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("drive the Motor Vehicle or has been so permitted and is not disqualified by order of a Court of Law or by reason", _fontNormal, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("of any enactment or regulation in that behalf from driving the Motor Vehicle.", _fontNormal, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);
        curY += lineSeparator;
        curY += 5;

        gfx.DrawString("6.  Limitations as to Use", _fontNormal, XBrushes.Black, new XRect(curX, curY, 500, 15), XStringFormat.TopLeft);
        curY += lineSeparator;
        newX -= 20;
        gfx.DrawString("USE ONLY FOR SOCIAL. DOMESTIC AND PLEASURE PURPOSES AND FOR THE POLICYHOLDER'S BUSINESS.", _courierFont, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);
        curY += 20;

        gfx.DrawString("THE POLICY DOES NOT COVER:", _courierFont, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("(i)   USE FOR HIRE OR REWARD.", _courierFont, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("(ii)  USE FOR RACING. PACE-MARKING. RELIABILITY TRIAL OR SPEED-TESTING.", _courierFont, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("(iii) USE FOR THE CARRIAGE OF GOODS (OTHER THAN SAMPLES) IN CONNECTION WITH ANY TRADE OR BUSINESS.", _courierFont, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("(iv) USE FOR ANY PURPOSE IN CONNECTION WITH THE MOTOR TRADE.", _courierFont, XBrushes.Black, new XRect(newX, curY, 500, 15), XStringFormat.TopLeft);
        curY += 70;

        gfx.DrawString("* Limitations rendered inoperative by Section 8 of the Motor Vehicles (Third-Party Risks and Compensation) Act (Chapter 189)", _fontNormal, XBrushes.Black, new XRect(curX, curY, 500, 15), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("and Section 95 of the Road Transport Act, 1987 (Malaysia), are not to be included under these headings.", _fontNormal, XBrushes.Black, new XRect(curX, curY, 500, 15), XStringFormat.TopLeft);
        curY += lineSeparator;
        curY += 10;
        //Bottom Text
        gfx.DrawString("I/WE HERE CERTIFIY that the policy to which this Certificate relates is issued in accordance with the provisions of the Motor Vehicles (Third-Party ", _fontNormal, XBrushes.Black, new XRect(curX, curY, 500, 15), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("Risks and Compensation) Act (Chapter 189) and Part IV of the Road Transport Act, 1987 (Malaysia).", _fontNormal, XBrushes.Black, new XRect(curX, curY, 500, 15), XStringFormat.TopLeft);
        curY += lineSeparator;

        curY += lineSeparator;
        newX += 270;
        gfx.DrawString("For and on behalf of Etiqa Insurance Pte Ltd", _fontNormal, XBrushes.Black, new XRect(newX, curY, 300, 15), XStringFormat.TopCenter);
        curY += 10;
        gfx.DrawString("Approved Insurer", _fontNormal, XBrushes.Black, new XRect(newX, curY, 300, 15), XStringFormat.TopCenter);
        curY += lineSeparator;
        curY += lineSeparator;
        curY += lineSeparator;
        gfx.DrawString("Authorised Sinigature", _fontNormal, XBrushes.Black, new XRect(newX, curY, 300, 15), XStringFormat.TopCenter);
        curY += lineSeparator;

        PdfDocument inputDocument = PdfReader.Open(Server.MapPath("~/images/pdf/private_motor_last_two_pages.pdf"), PdfDocumentOpenMode.Import);

        document.AddPage(inputDocument.Pages[0]);
        document.AddPage(inputDocument.Pages[1]);
        document.AddPage(inputDocument.Pages[2]);
        /*
        // Iterate pages
        int count = inputDocument.PageCount;
        for (int idx = 0; idx < count; idx++)
        {
            // Get the page from the external document...
            PdfPage _temppage = inputDocument.Pages[idx];
            // ...and add it to the output document.
            document.AddPage(page);
            _temppage = null;
        }
         */

        string filename = "~/TestPdfCsharp/" + "Etiqa_certificate_tempfile2.pdf";
        //document.Save(filename);
        // ...and start a viewer.
        //Response.Redirect(filename);
        System.IO.MemoryStream stream = new System.IO.MemoryStream();
        document.Save(Server.MapPath(filename));
        stream.Close();
        document.Close();
        return filename;
        //Response.Redirect(filename);
    }

    private string EtiqaTaxInvoiceOutputSample()
    {
        string _customerName, _customerAddress1, _customerAddress2;
        _customerName = ""; _customerAddress1 = ""; _customerAddress2 = "";
        string _orderNo = Request.Params["ordernumber"].ToString();

        string _premium = GetPremiumDetails(_orderNo);
        string[] Premium = _premium.Split(',');

        DataSet _dsPolicy = new DataSet();
        EtiqaCarMoreDetail _etiqacarmoredetail = new EtiqaCarMoreDetail();
        EtiqaCarMoreDetailDB _etiqacarmoredetaildb = new EtiqaCarMoreDetailDB();
        _dsPolicy = _etiqacarmoredetaildb.GetPolicyDetails(_orderNo);
        if (_dsPolicy != null && _dsPolicy.Tables[0].Rows.Count > 0)
        {
            _customerName = _dsPolicy.Tables[0].Rows[0]["Name"].ToString();
            _customerAddress1 = "#" + _dsPolicy.Tables[0].Rows[0]["UnitNo"].ToString() + " - " + _dsPolicy.Tables[0].Rows[0]["HomeNo"].ToString() + ", " + _dsPolicy.Tables[0].Rows[0]["Address"].ToString();
            _customerAddress2 = "Singapore - " + _dsPolicy.Tables[0].Rows[0]["PostalCode"].ToString();
        }
        // Create a new PDF document
        PdfDocument document = new PdfDocument();
        document.Info.Title = "Etiqa - Tax";

        // Create an empty page
        PdfPage page = document.AddPage();

        // Get an XGraphics object for drawing
        XGraphics gfx = XGraphics.FromPdfPage(page);

        //XPdfFontOptions options = new XPdfFontOptions(PdfFontEncoding.Unicode, PdfFontEmbedding.Always);

        // Create a font
        XFont font = new XFont("Verdana", 10, XFontStyle.Bold);

        XColor _xcolor = new XColor();
        _xcolor.R = 0;
        _xcolor.G = 0;
        _xcolor.B = 0;
        System.Drawing.Rectangle _rect = new System.Drawing.Rectangle(20, 10, 500, 100);
        XPen _pen = new XPen(_xcolor);

        int curX = 50;
        int curY = 50;

        gfx.DrawImage(XImage.FromFile(Server.MapPath("~/images/pdf/header_etiqa_01.png")), new XRect(curX, curY, 520, 61));
        curY += 65;

        //gfx.DrawString("Your new friends list", font, XBrushes.Black, new XRect(curX, curY, 50, 20), XStringFormat.TopLeft);
        //curY += 20;
        int _boxWidth = Convert.ToInt32(page.Width.Value) - 100;

        DrawBoxWithText(gfx, _xcolor, curX, curY, _boxWidth, 20, 0.8d, "TAX INVOICE", "Verdana", 10.0d, XFontStyle.Bold);
        curY += 25;

        _xcolor.R = 255;
        _xcolor.G = 255;
        _xcolor.B = 255;

        DrawBoxWithText(gfx, _xcolor, curX, curY, _boxWidth, 20, 0, "Debit Note", "Verdana", 10.0d, XFontStyle.Bold);
        curY += 20;

        string _gstRegNumber = "M90363266Y";
        string _transDate = CE.Global.General.GetUTCDateTime().ToShortDateString();
        //string _customerName = "BRYAN YU CHEE SIANG";
        //string _customerAddress1 = "123, JALAN 222";
        //string _customerAddress2 = "SINGAPORE 604565";

        XFont _textFont = new XFont("Verdana", 8, XFontStyle.Bold);
        gfx.DrawString("GST Reg. No. " + _gstRegNumber, _textFont, XBrushes.Black, new XRect(curX, curY, 50, 15), XStringFormat.TopLeft);
        gfx.DrawString("Transaction / Due Date : " + _transDate, _textFont, XBrushes.Black, new XRect(curX + 350, curY, 50, 15), XStringFormat.TopLeft);
        curY += 30;

        gfx.DrawString(_customerName, _textFont, XBrushes.Black, new XRect(curX + 30, curY, 50, 15), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString(_customerAddress1, _textFont, XBrushes.Black, new XRect(curX + 30, curY, 50, 15), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString(_customerAddress2 + _transDate, _textFont, XBrushes.Black, new XRect(curX + 30, curY, 50, 15), XStringFormat.TopLeft);
        curY += 30;

        string[,] _arrNewData = new string[,]{{"Type Of Policy", "Car Insurance"},
                          {"Policy Number", "DPMP0000001"},
                          {"Period of Cover", "From 28/04/2010 to 27/04/2011 (both dates inclusive)"},
                          {"Main Insured's Name", "BRYAN YU CHEE SIANG"},
                          {"Insured's Address", "123, JALAN 222"},
                          {"", "SINGAPORE 604565"},
        {"Branch / Territory","SINGAPORE / Singapore"}};

        int[] _colArrWidth = { 180, 300 };

        _xcolor.R = 0;
        _xcolor.G = 0;
        _xcolor.B = 0;

        DrawBoxWithTextList(gfx, _xcolor, curX, curY, _boxWidth, 12, 7, 2, 1.0d, "Verdana", 10, XFontStyle.Regular, _arrNewData, _colArrWidth);

        curY += 180;

        string[,] _arrPremiumData = new string[,]{{"Annual Premium (Inclusive of 7% GST)", "           S$        206.72"},{"", "_________________________"},
                          {"Total Due", "           S$        206.72"},
                          };

        _xcolor.R = 0;
        _xcolor.G = 0;
        _xcolor.B = 0;
        int[] _colPremiumArrWidth = { 300, 150 };
        DrawBoxWithTextList(gfx, _xcolor, curX, curY, _boxWidth, 10, 3, 2, 1.0d, "Verdana", 10, XFontStyle.Regular, _arrPremiumData, _colPremiumArrWidth);

        curY += 90;
        gfx.DrawString("Signed for and on behalf of the company", _textFont, XBrushes.Black, new XRect(curX, curY, 50, 15), XStringFormat.TopLeft);
        curY += 30;
        gfx.DrawString("___________________________________", _textFont, XBrushes.Black, new XRect(curX, curY, 50, 15), XStringFormat.TopLeft);
        curY += 10;
        gfx.DrawString("Authorized Signature", _textFont, XBrushes.Black, new XRect(curX, curY, 50, 15), XStringFormat.TopLeft);

        string filename = "~/TestPdfCsharp/" + "Etiqa_tempfile2.pdf";
        //document.Save(filename);
        // ...and start a viewer.
        //Response.Redirect(filename);
        System.IO.MemoryStream stream = new System.IO.MemoryStream();
        document.Save(Server.MapPath(filename));
        stream.Close();
        document.Close();
        //Response.Redirect(filename);
        return filename;
    }

    void SampleOutput()
    {
        // Create a new PDF document
        PdfDocument document = new PdfDocument();
        document.Info.Title = "Created with PDFsharp";

        // Create an empty page
        PdfPage page = document.AddPage();

        // Get an XGraphics object for drawing
        XGraphics gfx = XGraphics.FromPdfPage(page);

        //XPdfFontOptions options = new XPdfFontOptions(PdfFontEncoding.Unicode, PdfFontEmbedding.Always);

        // Create a font
        XFont font = new XFont("Verdana", 10, XFontStyle.Bold);

        XColor _xcolor = new XColor();
        _xcolor.R = 0;
        _xcolor.G = 0;
        _xcolor.B = 0;
        System.Drawing.Rectangle _rect = new System.Drawing.Rectangle(10, 10, 500, 100);
        XPen _pen = new XPen(_xcolor);
        // Draw the text
        //gfx.DrawRectangle(_pen, _rect);
        //gfx.DrawString("Hello, World!", font, XBrushes.Black,
        //  new XRect(15, 15, 15, 15),
        //  XStringFormats.TopLeft);
        int curX = 0;
        int curY = 0;

        gfx.DrawString("Your new friends list", font, XBrushes.Black, new XRect(10, 10, 50, 20), XStringFormat.TopLeft);
        curX = 10;
        curY = 20;

        string[] _arrHeader = { "Name", "Email", "NRIC", "Post Code" };
        int[] _colWidth = { 70, 100, 50, 40 };
        string[,] _arrData = new string[3, 4]{{"Phyo Kyaw Thant", "phyokyawthant@gmail.com", "S1234567D", "140078"},
                          {"Htay Aung Kyaw", "hak.aung@gmail.com", "S1234567D", "140078"},
                          {"Mee Nge", "meenge.ktha@gmail.com", "S1234567D", "140078"}
                      };
        DrawTable(gfx, curX, curY + 5, 3, 4, 15, _colWidth, _arrHeader, _arrData);
        curX = 10;
        curY += (5 + (3 * 15));

        curY += 10;
        gfx.DrawString("Your other friends list", font, XBrushes.Black, new XRect(10, curY + 20, 50, 20), XStringFormat.TopLeft);

        curX = 10;
        curY += 10;
        string[] _arrNewHeader = { "Name", "Email", "NRIC", "Post Code" };
        int[] _colNewWidth = { 70, 100, 50, 40 };
        curX = 10;
        curY += (5 + 15);

        string[,] _arrNewData = new string[3, 4]{{"Nay Myo Aung", "mgnaygyi@gmail.com", "S1234567D", "140078"},
                          {"San Myo Aung", "sannaung@gmail.com", "S1234567D", "140078"},
                          {"Theingyi Kyaw", "khintheingikyaw@gmail.com", "S1234567D", "140078"}
                      };
        DrawTable(gfx, curX, curY + 5, 3, 4, 15, _colNewWidth, _arrNewHeader, _arrNewData);

        // Save the document...
        string filename = "~/TestPdfCsharp/" + "HelloWorld_tempfile1.pdf";
        //document.Save(filename);
        // ...and start a viewer.
        //Response.Redirect(filename);
        System.IO.MemoryStream stream = new System.IO.MemoryStream();
        document.Save(Server.MapPath(filename));
        Response.Redirect(filename);
        //document.Save(stream, false);
        //Response.Clear();
        //Response.ContentType = "application/pdf";
        //Response.AddHeader("content-length", stream.Length.ToString());
        //Response.BinaryWrite(stream.ToArray());
        //Response.Flush();
        //stream.Close();
        //Response.End();
    }

    private void DrawBoxWithText(XGraphics _objG, XColor _xcolor, int x, int y, int width, int height, double borderWidth, string strText, string strFontName, double fontSize, XFontStyle xFontStyle)
    {

        System.Drawing.Rectangle _rect = new System.Drawing.Rectangle(0, 0, 0, 0);
        _rect.X = x;
        _rect.Y = y;
        _rect.Width = width;
        _rect.Height = height;
        XPen _pen = new XPen(_xcolor);
        _pen.Width = borderWidth;
        XFont font = new XFont(strFontName, fontSize, xFontStyle);
        _objG.DrawRectangle(_pen, _rect);
        _objG.DrawString(strText, font, XBrushes.Black, _rect, XStringFormats.Center);
    }

    private void DrawBoxWithTextList(XGraphics _objG, XColor _xcolor, int x, int y, int width, int height, int rows, int cols, double borderWidth, string strFontName, double fontSize, XFontStyle xFontStyle, string[,] _arrData, int[] _arrColsWidth)
    {

        System.Drawing.Rectangle _rect = new System.Drawing.Rectangle(0, 0, 0, 0);
        _rect.X = x;
        _rect.Y = y;
        _rect.Width = width;
        _rect.Height = (height + 8) * rows;

        XColor _xOuterBoxColor = new XColor();
        _xOuterBoxColor.R = 0;
        _xOuterBoxColor.G = 0;
        _xOuterBoxColor.B = 0;

        XPen _pen = new XPen(_xcolor);
        _pen.Width = borderWidth;
        XFont font = new XFont(strFontName, fontSize, xFontStyle);
        XFont boldFont = new XFont(strFontName, fontSize, XFontStyle.Bold);
        _objG.DrawRectangle(_pen, _rect);

        System.Drawing.Rectangle _rectText = new System.Drawing.Rectangle(0, 0, 0, 0);
        _rectText.X = x + 5;
        _rectText.Y = y + 5;
        _rectText.Width = width;
        _rectText.Height = height;

        for (int i = 0; i < rows; i++)
        {
            // Draw the text
            for (int j = 0; j < cols; j++)
            {
                _rectText.Width = _arrColsWidth[j];
                if (j == 0)
                {
                    _objG.DrawString(_arrData[i, j].ToString(), boldFont, XBrushes.Black, _rectText, XStringFormats.TopLeft);
                }
                else
                {
                    _objG.DrawString(_arrData[i, j].ToString(), font, XBrushes.Black, _rectText, XStringFormats.TopLeft);
                }
                _rectText.X += _arrColsWidth[j];
            }
            _rectText.X = x + 5;
            _rectText.Y += height + 5;
        }

    }

    private void DrawTable(XGraphics _objG, int x, int y, int rows, int cols, int rowHeight, int[] _arrColsWidth, string[] _arrHeader, string[,] _arrData)
    {
        XColor _xcolor = new XColor();
        _xcolor.R = 0;
        _xcolor.G = 0;
        _xcolor.B = 0;
        System.Drawing.Rectangle _rect = new System.Drawing.Rectangle(0, 0, 0, 0);
        _rect.X = x;
        _rect.Y = y;
        _rect.Width = _arrColsWidth[0];
        _rect.Height = rowHeight;
        XPen _pen = new XPen(_xcolor);
        _pen.Width = 0.5d;
        XFont font = new XFont("Verdana", 5, XFontStyle.Regular);

        for (int h = 0; h < cols; h++)
        {
            _rect.Width = _arrColsWidth[h];
            _objG.DrawRectangle(_pen, _rect);
            _objG.DrawString(_arrHeader[h].ToString(), font, XBrushes.Black,
      new XRect(_rect.X + 3, _rect.Y + 3, 10, 10),
      XStringFormats.TopLeft);
            _rect.X += _arrColsWidth[h];
        }

        _rect.Y += rowHeight;
        _rect.X = x;

        for (int i = 0; i < rows; i++)
        {
            // Draw the text
            for (int j = 0; j < cols; j++)
            {
                _rect.Width = _arrColsWidth[j];

                _objG.DrawRectangle(_pen, _rect);
                _objG.DrawString(_arrData[i, j].ToString(), font, XBrushes.Black,
          new XRect(_rect.X + 3, _rect.Y + 3, 10, 10),
          XStringFormats.TopLeft);
                _rect.X += _arrColsWidth[j];
            }
            _rect.Y += rowHeight;
            _rect.X = x;
        }
    }

    private void GetUserInformation(Account _account)
    {
        DateTime dtNow = new DateTime();
        dtNow = CE.Global.General.GetUTCDateTime();
        Session[CE.Global.General.UserId] = _account.CEID;
        Session[CE.Global.General.UserTypeId] = _account.UserTypeId;

        if (_account.UserTypeId == Convert.ToInt32(CE.Global.CEUserType.Provider))
        {
            CE.Provider.ProviderCompanyLink _comlink = new CE.Provider.ProviderCompanyLink();
            CE.Provider.ProviderCompanyLinkDB _comlinkdb = new CE.Provider.ProviderCompanyLinkDB();
            _comlink = _comlinkdb.GetProviderCompanyId(_account.CEID);
            if (_comlink != null)
            {
                Session[CE.Global.General.ProviderCompanyId] = _comlink.ProviderCompanyId;
            }
        }

        Session[CE.Global.General.UserEmail] = _account.Email;
        Session[CE.Global.General.StartTime] = dtNow.ToString();
        bool isProfileComplete = false;
        bool isContactComplete = false;

        ProfileDB _profiledb = new ProfileDB();
        Profile _profile = new Profile();
        Contact _contact = new Contact();
        ContactDB _contactdb = new ContactDB();

        _contact = _contactdb.GetContact(_account.CEID);

        _profile = _profiledb.GetProfile(_account.CEID);



        if (_contact != null)
        {
            isContactComplete = true;
            Session[CE.Global.General.UserPhoneNo] = _contact.Mobile;
        }
        else
        {
            Session[CE.Global.General.UserPhoneNo] = "No Phone No";
        }

        if (_profile != null)
        {
            if (_profile.FirstName != null && _profile.FirstName != string.Empty)
            {
                Session[CE.Global.General.UserName] = Server.HtmlDecode(_profile.SurName) + " " + Server.HtmlDecode(_profile.FirstName);
            }
            else
            {
                Session[CE.Global.General.UserName] = Server.HtmlDecode(_profile.SurName);
            }

            if (!string.IsNullOrEmpty(_profile.IDType) &&
                !string.IsNullOrEmpty(_profile.IDNo) &&
                _profile.IDNo.Length == 9)
            {
                Session[CE.Global.General.UserICNumber] = _profile.IDType + " " + _profile.IDNo;
            }

            if (_profile.Status.ToLower() == "complete")
            {
                isProfileComplete = true;
            }
            else
            {
                isProfileComplete = false;
            }
        }
        else
        {
            Session[CE.Global.General.UserName] = "No Name";
        }

        if (isProfileComplete && isContactComplete)
        {
            Session[CE.Global.General.UserProfileAndContactComplete] = "1";
        }
        else
        {
            Session[CE.Global.General.UserProfileAndContactComplete] = "0";
        }

    }

    protected string GenerateProposalFormToEtiqa(Int64 RefID, string FilePath, string FileName)
    {
        string _returnFilePath = "";
        //string _filePath = FilePath;
        Label lblContent = new Label();
        //string _fileName = FileName;
        string _applicationdate = " : " + CE.Global.General.GetUTCDateTime().ToString("dd/MMM/yyyy") + " " + CE.Global.General.GetUTCDateTime().ToShortTimeString();
        //string convertno = General.GetRefNumber(RefID);
        //_fileName = "E" + convertno + "_" + CE.Global.General.GetUTCDateTime().Day.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Month.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Year;

        StringBuilder strText = new StringBuilder("");
        strText.Append("<table><tr><td colspan='2'><h4>Car Insurance - Proposal Form</h4></td><td colspan='2' align='right'><b>Application Date</b> " + _applicationdate.ToString() + "</td></tr></table>");

        DataSet _dsProposal = new DataSet();
        ProposalDownloadDB _proposaldownloaddb = new ProposalDownloadDB();
        _dsProposal = _proposaldownloaddb.GetEtiqaProposal(RefID, "CI");
        string _html = "";
        try
        {
            if (_dsProposal != null && _dsProposal.Tables[0].Rows.Count > 0)
            {
                #region Variables

                string _name, _unitno, _homeno, _address, _postalcode, _dob, _dd, _mm, _yy, _nationality, _nric, _maritalstatus, _mobile, _occupation;
                string _gender, _expdriving, _vehicleno, _make, _model, _vehicletype, _engineno, _chassisno, _cc, _regyear, _hpstatus, _hpcomp;
                string _insstartdate, _insenddate, _isdd, _ismm, _isyy, _iedd, _iemm, _ieyy, _insstatus, _inscomid, _inscomp, _inspolicyno;
                string _inspldate, _indd, _inmm, _inyy;
                string _drname1, _drdob1, _drdd1, _drmm1, _dryy1, _drocc1, _drexp1, _drname2, _drdob2, _drdd2, _drmm2, _dryy2, _drocc2, _drexp2;
                string _drname3, _drdob3, _drdd3, _drmm3, _dryy3, _drocc3, _drexp3, _drname4, _drdob4, _drdd4, _drmm4, _dryy4, _drocc4, _drexp4;
                string _drname5, _drdob5, _drdd5, _drmm5, _dryy5, _drocc5, _drexp5, _demeritstatus, _demeritcount, _dmdrname1, _dmdrpoint1;
                string _dmdrname2, _dmdrpoint2, _dmdrname3, _dmdrpoint3, _dmdrname4, _dmdrpoint4, _dmdrname5, _dmdrpoint5, _dmdrname6, _dmdrpoint6;
                string _adddriver, _drcount, _claimstatus, _claim1ststat, _claim1stend;
                string claimamt1, _claim2ndstat, _claim2ndend, claimamt2, _claim3rdstat, _claim3rdend, claimamt3, _ncdstatus, _ncdpercent;
                string _inscancelstatus, _cancelreason, _impairstatus, _impairreason, _planname, _annualpremium;
                string _excess, _email, _natureofbusi, _cxaccountno, _aucode, _policyno, _orderno, _nextNCD, _isOFD, _isNCDProtector;
                DateTime _dofb, _isdate, _iedate, _indate, _drdofb1, _drdofb2, _drdofb3, _drdofb4, _drdofb5;

                _inscancelstatus = ""; _cancelreason = ""; _impairstatus = ""; _impairreason = ""; _demeritstatus = ""; _demeritcount = "";
                _dmdrname1 = ""; _dmdrpoint1 = ""; _dmdrname2 = ""; _dmdrpoint2 = ""; _dmdrname3 = ""; _dmdrpoint3 = ""; _dmdrname4 = "";
                _dmdrpoint4 = ""; _dmdrname5 = ""; _dmdrpoint5 = ""; _dmdrname6 = ""; _dmdrpoint6 = ""; _vehicletype = ""; _expdriving = "";
                _drname1 = ""; _drdob1 = ""; _drdd1 = ""; _drmm1 = ""; _dryy1 = ""; _drocc1 = ""; _drexp1 = "";
                _drname2 = ""; _drdob2 = ""; _drdd2 = ""; _drmm2 = ""; _dryy2 = ""; _drocc2 = ""; _drexp2 = "";
                _drname3 = ""; _drdob3 = ""; _drdd3 = ""; _drmm3 = ""; _dryy3 = ""; _drocc3 = ""; _drexp3 = "";
                _drname4 = ""; _drdob4 = ""; _drdd4 = ""; _drmm4 = ""; _dryy4 = ""; _drocc4 = ""; _drexp4 = "";
                _drname5 = ""; _drdob5 = ""; _drdd5 = ""; _drmm5 = ""; _dryy5 = ""; _drocc5 = ""; _drexp5 = ""; _planname = ""; _annualpremium = "";
                _adddriver = ""; _drcount = "";

                #endregion

                #region Assign Values

                _name = _dsProposal.Tables[0].Rows[0]["Name"].ToString();
                _dofb = Convert.ToDateTime(_dsProposal.Tables[0].Rows[0]["DOB"].ToString());
                _dd = _dofb.Day.ToString("00");
                _mm = _dofb.Month.ToString("00");
                _yy = _dofb.Year.ToString();
                //_dob = _dd + "/" + _mm + "/" + _yy;
                _dob = _dsProposal.Tables[0].Rows[0]["DOB"].ToString();
                _nationality = _dsProposal.Tables[0].Rows[0]["Nationality"].ToString();
                _unitno = _dsProposal.Tables[0].Rows[0]["UnitNo"].ToString();
                _homeno = _dsProposal.Tables[0].Rows[0]["HomeNo"].ToString();
                _address = _dsProposal.Tables[0].Rows[0]["Address"].ToString();
                _postalcode = _dsProposal.Tables[0].Rows[0]["PostalCode"].ToString();
                _nric = _dsProposal.Tables[0].Rows[0]["NRICNo"].ToString();
                _maritalstatus = _dsProposal.Tables[0].Rows[0]["Marital"].ToString();
                _gender = _dsProposal.Tables[0].Rows[0]["Gender"].ToString();

                _email = _dsProposal.Tables[0].Rows[0]["Email"].ToString();                
                _natureofbusi = _dsProposal.Tables[0].Rows[0]["NofBusi"].ToString();
                _nextNCD = _dsProposal.Tables[0].Rows[0]["NCDNextPercent"].ToString();
                _isNCDProtector = _dsProposal.Tables[0].Rows[0]["IsNCDProtector"].ToString(); ;
                _isOFD = _dsProposal.Tables[0].Rows[0]["IsOffDriveCom"].ToString();

                _planname = _dsProposal.Tables[0].Rows[0]["PlanName"].ToString();
                _annualpremium = _dsProposal.Tables[0].Rows[0]["AnnualPremium"].ToString();

                _expdriving = _dsProposal.Tables[0].Rows[0]["YearDriveSg"].ToString();
                _occupation = _dsProposal.Tables[0].Rows[0]["Description"].ToString();
                _mobile = _dsProposal.Tables[0].Rows[0]["ContactNo"].ToString();

                _excess = _dsProposal.Tables[0].Rows[0]["Excess"].ToString();
                //if (_planname.Trim() == "Panel Workshops")
                //{
                //    double _dexcess = (double.Parse(_excess)) / 2;
                //    _excess = _dexcess.ToString();
                //}

                if (_dsProposal.Tables[0].Rows[0]["IsClaimLastThreeYear"].ToString() == "False")
                {
                    _claimstatus = "No";
                }
                else
                {
                    _claimstatus = "Yes";
                }
                if (_dsProposal.Tables[0].Rows[0]["HasPreInsurer"].ToString() == "True")
                {
                    _insstatus = "Yes";
                    if (_dsProposal.Tables[2].Rows.Count > 0)
                    {
                        _inscomp = _dsProposal.Tables[2].Rows[0]["InsCompanyName"].ToString();
                        _inspolicyno = _dsProposal.Tables[0].Rows[0]["PreInsPolicyNo"].ToString();
                        _indate = Convert.ToDateTime(_dsProposal.Tables[0].Rows[0]["PreInsExpiryDate"].ToString());
                        _indd = _indate.Day.ToString("00");
                        _inmm = _indate.Month.ToString("00");
                        _inyy = _indate.Year.ToString();
                        _inspldate = _indd + "/" + _inmm + "/" + _inyy;
                        //_inspldate = _dsProposal.Tables[0].Rows[0]["PreInsExpiryDate"].ToString();
                        _inscomid = _dsProposal.Tables[0].Rows[0]["PreInsurerName"].ToString();
                    }
                    else
                    {
                        _insstatus = "No";
                        _inscomp = "Not Applicable";
                        _inspolicyno = "";
                        _inspldate = "";
                        _inscomid = "01";
                    }
                }
                else
                {
                    _insstatus = "No";
                    _inscomp = "Not Applicable";
                    _inspolicyno = "";
                    _inspldate = "";
                    _inscomid = "01";
                }


                _cxaccountno = _dsProposal.Tables[0].Rows[0]["CXAccountNo"].ToString();
                _policyno = _dsProposal.Tables[0].Rows[0]["PolicyNumber"].ToString();
                _aucode = _dsProposal.Tables[0].Rows[0]["AuCode"].ToString();
                _orderno = _dsProposal.Tables[0].Rows[0]["OrderNo"].ToString();


                _ncdpercent = _dsProposal.Tables[0].Rows[0]["NCDPercent"].ToString();
                if (_ncdpercent.ToString() == "-1")
                {
                    _ncdpercent = "No NCD(First Policy)";
                }
                else
                {
                    _ncdpercent += "%";
                }

                if (_dsProposal.Tables[1].Rows.Count > 0)
                {
                    _claim1ststat = _dsProposal.Tables[1].Rows[0]["Claim1stStartDate"].ToString();
                    _claim1stend = _dsProposal.Tables[1].Rows[0]["Claim1stEndDate"].ToString();
                    claimamt1 = _dsProposal.Tables[1].Rows[0]["NOCLast1Value"].ToString();

                    _claim2ndstat = _dsProposal.Tables[1].Rows[0]["Claim2ndStartDate"].ToString();
                    _claim2ndend = _dsProposal.Tables[1].Rows[0]["Claim2ndEndDate"].ToString();
                    claimamt2 = _dsProposal.Tables[1].Rows[0]["NOCLast2Value"].ToString();

                    _claim3rdstat = _dsProposal.Tables[1].Rows[0]["Claim3rdStartDate"].ToString();
                    _claim3rdend = _dsProposal.Tables[1].Rows[0]["Claim3rdEndDate"].ToString();
                    claimamt3 = _dsProposal.Tables[1].Rows[0]["NOCLast3Value"].ToString();
                }
                else
                {
                    _claim1ststat = "";
                    _claim1stend = "";
                    claimamt1 = "";

                    _claim2ndstat = "";
                    _claim2ndend = "";
                    claimamt2 = "";

                    _claim3rdstat = "";
                    _claim3rdend = "";
                    claimamt3 = "";
                    _claimstatus = "No";
                }
                if (_dsProposal.Tables[0].Rows[0]["HasInsuranceTerminated"].ToString() == "True")
                {
                    _inscancelstatus = "Yes";
                    _cancelreason = _dsProposal.Tables[0].Rows[0]["InsuranceTerminatedReason"].ToString();
                }
                else
                {
                    _inscancelstatus = "No";
                    _cancelreason = "";
                }

                if (_dsProposal.Tables[0].Rows[0]["HasPhysicalImpairment"].ToString() == "True")
                {
                    _impairstatus = "Yes";
                    _impairreason = _dsProposal.Tables[0].Rows[0]["PhysicalImpairmentReason"].ToString();
                }
                else
                {
                    _impairstatus = "No";
                    _impairreason = "";
                }

                if (_dsProposal.Tables[0].Rows[0]["HasDemeritPoints"].ToString() == "True")
                {
                    if (_dsProposal.Tables[6].Rows.Count > 0)
                    {
                        _demeritstatus = "Yes";
                        _demeritcount = _dsProposal.Tables[6].Rows.Count.ToString();

                        _dmdrname1 = _dsProposal.Tables[6].Rows[0]["DriverName"].ToString();
                        _dmdrpoint1 = _dsProposal.Tables[6].Rows[0]["TotalDemeritPoints"].ToString();
                        if (_dsProposal.Tables[6].Rows.Count > 1)
                        {
                            _dmdrname2 = _dsProposal.Tables[6].Rows[1]["DriverName"].ToString();
                            _dmdrpoint2 = _dsProposal.Tables[6].Rows[1]["TotalDemeritPoints"].ToString();
                        }
                        if (_dsProposal.Tables[6].Rows.Count > 2)
                        {
                            _dmdrname3 = _dsProposal.Tables[6].Rows[2]["DriverName"].ToString();
                            _dmdrpoint3 = _dsProposal.Tables[6].Rows[2]["TotalDemeritPoints"].ToString();
                        }
                        if (_dsProposal.Tables[6].Rows.Count > 3)
                        {
                            _dmdrname4 = _dsProposal.Tables[6].Rows[3]["DriverName"].ToString();
                            _dmdrpoint4 = _dsProposal.Tables[6].Rows[3]["TotalDemeritPoints"].ToString();
                        }
                        if (_dsProposal.Tables[6].Rows.Count > 4)
                        {
                            _dmdrname5 = _dsProposal.Tables[6].Rows[4]["DriverName"].ToString();
                            _dmdrpoint5 = _dsProposal.Tables[6].Rows[4]["TotalDemeritPoints"].ToString();
                        }
                        if (_dsProposal.Tables[6].Rows.Count > 5)
                        {
                            _dmdrname6 = _dsProposal.Tables[6].Rows[5]["DriverName"].ToString();
                            _dmdrpoint6 = _dsProposal.Tables[6].Rows[5]["TotalDemeritPoints"].ToString();
                        }
                    }
                    else
                    {
                        _demeritstatus = "No";
                        _demeritcount = "0";
                    }
                }
                else
                {
                    _demeritstatus = "No";
                    _demeritcount = "0";
                }

                _vehicleno = _dsProposal.Tables[0].Rows[0]["VechicleNo"].ToString();
                _regyear = _dsProposal.Tables[0].Rows[0]["RegYear"].ToString();
                _model = _dsProposal.Tables[5].Rows[0]["Description"].ToString();
                _make = _dsProposal.Tables[5].Rows[0]["Make"].ToString(); ;
                _cc = _dsProposal.Tables[5].Rows[0]["CC"].ToString();
                _vehicletype = _dsProposal.Tables[5].Rows[0]["Body"].ToString();
                _engineno = _dsProposal.Tables[0].Rows[0]["EngineNo"].ToString();
                _chassisno = _dsProposal.Tables[0].Rows[0]["ChassisNo"].ToString();

                _isdate = Convert.ToDateTime(_dsProposal.Tables[0].Rows[0]["StartDate"].ToString());
                _isdd = _isdate.Day.ToString("00");
                _ismm = _isdate.Month.ToString("00");
                _isyy = _isdate.Year.ToString();
                _insstartdate = _isdd + "/" + _ismm + "/" + _isyy;
                //_insstartdate = _dsProposal.Tables[0].Rows[0]["StartDate"].ToString();

                _iedate = Convert.ToDateTime(_dsProposal.Tables[0].Rows[0]["EndDate"].ToString());
                _iedd = _iedate.Day.ToString("00");
                _iemm = _iedate.Month.ToString("00");
                _ieyy = _iedate.Year.ToString();
                _insenddate = _iedd + "/" + _iemm + "/" + _ieyy;
                //_insenddate = _dsProposal.Tables[0].Rows[0]["EndDate"].ToString();

                if (_dsProposal.Tables[3].Rows.Count > 0)
                {
                    _hpstatus = "Yes";
                    _hpcomp = _dsProposal.Tables[3].Rows[0]["HPCompanyName"].ToString();

                    if (_hpcomp.ToString() == "")
                    {
                        _hpcomp = "Not Applicable";
                    }
                }
                else
                {
                    _hpstatus = "No";
                    _hpcomp = "Not Applicable";
                }
                if (_dsProposal.Tables[0].Rows[0]["AddDriver"].ToString() == "Yes")
                {
                    _adddriver = "Yes";
                    _drcount = _dsProposal.Tables[0].Rows[0]["NoOfDriver"].ToString();
                }
                else
                {
                    _adddriver = "No";
                    _drcount = "0";
                }
                if (_dsProposal.Tables[4].Rows.Count > 0)
                {
                    if (Convert.ToInt16(_drcount) > 0)
                    {
                        _drname1 = _dsProposal.Tables[4].Rows[0]["DriverName"].ToString();
                        _drdofb1 = Convert.ToDateTime(_dsProposal.Tables[4].Rows[0]["DOB"].ToString());
                        _drdd1 = _drdofb1.Day.ToString("00");
                        _drmm1 = _drdofb1.Month.ToString("00");
                        _dryy1 = _drdofb1.Year.ToString();
                        //_drdob1 = _drdd1 + "/" + _drmm1 + "/" + _dryy1;
                        _drdob1 = _dsProposal.Tables[4].Rows[0]["DOB"].ToString();
                        _drocc1 = _dsProposal.Tables[4].Rows[0]["Description"].ToString();
                        _drexp1 = _dsProposal.Tables[4].Rows[0]["YearOfDrivingInSg"].ToString();
                    }
                    if (Convert.ToInt16(_drcount) > 1)
                    {
                        _drname2 = _dsProposal.Tables[4].Rows[1]["DriverName"].ToString();
                        _drdofb2 = Convert.ToDateTime(_dsProposal.Tables[4].Rows[1]["DOB"].ToString());
                        _drdd2 = _drdofb2.Day.ToString("00");
                        _drmm2 = _drdofb2.Month.ToString("00");
                        _dryy2 = _drdofb2.Year.ToString();
                        //_drdob2 = _drdd2 + "/" + _drmm2 + "/" + _dryy2;
                        _drdob2 = _dsProposal.Tables[4].Rows[1]["DOB"].ToString();
                        _drocc2 = _dsProposal.Tables[4].Rows[1]["Description"].ToString();
                        _drexp2 = _dsProposal.Tables[4].Rows[1]["YearOfDrivingInSg"].ToString();
                    }
                    if (Convert.ToInt16(_drcount) > 2)
                    {
                        _drname3 = _dsProposal.Tables[4].Rows[2]["DriverName"].ToString();
                        _drdofb3 = Convert.ToDateTime(_dsProposal.Tables[4].Rows[2]["DOB"].ToString());
                        _drdd3 = _drdofb3.Day.ToString("00");
                        _drmm3 = _drdofb3.Month.ToString("00");
                        _dryy3 = _drdofb3.Year.ToString();
                        //_drdob3 = _drdd3 + "/" + _drmm3 + "/" + _dryy3;
                        _drdob3 = _dsProposal.Tables[4].Rows[2]["DOB"].ToString();
                        _drocc3 = _dsProposal.Tables[4].Rows[2]["Description"].ToString();
                        _drexp3 = _dsProposal.Tables[4].Rows[2]["YearOfDrivingInSg"].ToString();
                    }
                    if (Convert.ToInt16(_drcount) > 3)
                    {
                        _drname4 = _dsProposal.Tables[4].Rows[3]["DriverName"].ToString();
                        _drdofb4 = Convert.ToDateTime(_dsProposal.Tables[4].Rows[3]["DOB"].ToString());
                        _drdd4 = _drdofb4.Day.ToString("00");
                        _drmm4 = _drdofb4.Month.ToString("00");
                        _dryy4 = _drdofb4.Year.ToString();
                        //_drdob4 = _drdd4 + "/" + _drmm4 + "/" + _dryy4;
                        _drdob4 = _dsProposal.Tables[4].Rows[3]["DOB"].ToString();
                        _drocc4 = _dsProposal.Tables[4].Rows[3]["Description"].ToString();
                        _drexp4 = _dsProposal.Tables[4].Rows[3]["YearOfDrivingInSg"].ToString();
                    }
                    if (Convert.ToInt16(_drcount) > 4)
                    {
                        _drname5 = _dsProposal.Tables[4].Rows[4]["DriverName"].ToString();
                        _drdofb5 = Convert.ToDateTime(_dsProposal.Tables[4].Rows[4]["DOB"].ToString());
                        _drdd5 = _drdofb5.Day.ToString("00");
                        _drmm5 = _drdofb5.Month.ToString("00");
                        _dryy5 = _drdofb5.Year.ToString();
                        //_drdob5 = _drdd5 + "/" + _drmm5 + "/" + _dryy5;
                        _drdob5 = _dsProposal.Tables[4].Rows[4]["DOB"].ToString();
                        _drocc5 = _dsProposal.Tables[4].Rows[4]["Description"].ToString();
                        _drexp5 = _dsProposal.Tables[4].Rows[4]["YearOfDrivingInSg"].ToString();
                    }
                }

                #endregion

                #region Excel Generation

                //_html = "<table><tr><td width='25%'><b>Plan Name</b></td><td>" + _planname + "</td><td colspan='2' align='right'><b>Annual Premium</b> : $" + _annualpremium.ToString() + "</td></tr></table>";
                //_html += "<div><div><b>Details of Proposer</b></div><div style='text-align: center'>"
                _html = "<table><tr><td width='25%'><b>Plan Name</b></td><td>" + _planname + "</td><td colspan='2' align='right'><b>Annual Premium</b> : $" + _annualpremium.ToString() + "</td></tr>";
                _html += "<tr><td colspan='2'><b>Details of Proposer</b></td><td colspan='2' align='right'><b>Excess Amount</b> : $" + _excess.ToString() + "</td></tr></table>";
                _html += "<div><div style='text-align: center'>"
                         + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0' border='1'>"
                         + "<tr>"
                         + "<td width='50%'>" //Start 1
                         + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0' border='1'>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Name</td>"
                         + "<td width='50%' align='left'>" + _name.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td align='left'>Address</td>"
                         + "<td align='left'>"
                         + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0'>"
                         + "<tr><td align='left'>#" + _unitno.ToString() + " - " + _homeno.ToString() + "</td></tr>"
                         + "<tr><td align='left'>" + _address.ToString() + "</td></tr>"
                         + "<tr><td align='left'>Postal Code " + _postalcode.ToString() + "</td></tr>"
                         + "</table>"
                         + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Email</td>"
                         + "<td width='50%' align='left'>" + _email.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Occupation / Business</td>"
                         + "<td width='50%' align='left'>" + _occupation.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Contact No</td>"
                         + "<td width='50%' align='left'>" + _mobile.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Existing NCD</td>"
                         + "<td width='50%' align='left'>" + _ncdpercent.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>OFD</td>"
                         + "<td width='50%' align='left'>" + _isOFD.ToString() + "</td>"
                         + "</tr>"

                         + "</table>"
                         + "</td>" //End 1

                         + "<td width='50%'>" //Start 2
                         + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0' border='1'>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Date of Birth</td>"
                         + "<td width='50%' style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _dob.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Nationality</td>"
                         + "<td width='50%' align='left'>" + _nationality.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>NRIC Number</td>"
                         + "<td width='50%' align='left'>" + _nric.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Sex</td>"
                         + "<td width='50%' align='left'>" + _gender.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Marital Status</td>"
                         + "<td width='50%' align='left'>" + _maritalstatus.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Nature of Business</td>"
                         + "<td width='50%' align='left'>" + _natureofbusi.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Driving Experience</td>"
                         + "<td width='50%' align='left'>" + _expdriving.ToString() + "</td>"
                         + "</tr>"

                          + "<tr>"
                         + "<td width='50%' align='left'>NCD upon renewal</td>"
                         + "<td width='50%' align='left'>" + _nextNCD.ToString() + "</td>"
                         + "</tr>"

                          + "<tr>"
                         + "<td width='50%' align='left'>Is NCDProtector</td>"
                         + "<td width='50%' align='left'>" + _isNCDProtector.ToString() + "</td>"
                         + "</tr>"

                         + "</table>"
                         + "</td>" //End 2
                         + "</tr>"

                         + "<tr>"
                         + "<td colspan='2'>"
                         + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0' border='1'>"

                         + "<tr><td colspan='4' align='left'><b>Particulars of Vehicle</b></td></tr>"

                         + "<tr><td style='width:25%' align='left'>Registration No</td>"
                         + "<td style='width: 25%' align='left'>" + _vehicleno.ToString() + "</td>"
                         + "<td style='width: 25%' align='left'>Year of Registration</td>"
                         + "<td style='width: 25%' align='left'>" + _regyear.ToString() + "</td></tr>"

                         + "<tr><td style='width:25%' align='left'>Make of Vehicle</td>"
                         + "<td style='width: 25%' align='left'>" + _make.ToString() + "</td>"
                         + "<td style='width: 25%' align='left'>Vehicle Model</td>"
                         + "<td style='width: 25%' align='left'>" + _model.ToString() + "</td></tr>"

                         + "<tr><td style='width:25%' align='left'>Vehicle Type</td>"
                         + "<td style='width: 25%' align='left'>" + _vehicletype.ToString() + "</td>"
                         + "<td style='width: 25%' align='left'>Engine capacity</td>"
                         + "<td style='width: 25%' align='left'>" + _cc.ToString() + "cc</td></tr>"

                         + "<tr><td style='width:25%' align='left'>Engine No</td>"
                         + "<td style='width: 25%' align='left'>" + _engineno.ToString() + "</td>"
                         + "<td style='width: 25%' align='left'>Chassis No</td>"
                         + "<td style='width: 25%' align='left'>" + _chassisno.ToString() + "</td></tr>"

                         + "<tr><td colspan='4' align='left'><b>Additional drivers : </b>" + _adddriver.ToString() + "</td></tr>";

                if (Convert.ToInt16(_drcount) > 0)
                {
                    _html += "<tr><td colspan='4' align='left'>Driver 1</td></tr>"
                    + "<tr><td style='width:25%' align='left'>Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _drname1.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Date of Birth</td>"
                    + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _drdob1.ToString() + "</td></tr>"

                    + "<tr><td style='width:25%' align='left'>Occupation</td>"
                    + "<td style='width: 25%' align='left'>" + _drocc1.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Driving Experience</td>"
                    + "<td style='width: 25%' align='left'>" + _drexp1.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_drcount) > 1)
                {
                    _html += "<tr><td colspan='4' align='left'>Driver 2</td></tr>"
                    + "<tr><td style='width:25%' align='left'>Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _drname2.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Date of Birth</td>"
                    + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _drdob2.ToString() + "</td></tr>"

                    + "<tr><td style='width:25%' align='left'>Occupation</td>"
                    + "<td style='width: 25%' align='left'>" + _drocc2.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Driving Experience</td>"
                    + "<td style='width: 25%' align='left'>" + _drexp2.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_drcount) > 2)
                {
                    _html += "<tr><td colspan='4' align='left'>Driver 3</td></tr>"
                    + "<tr><td style='width:25%' align='left'>Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _drname3.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Date of Birth</td>"
                    + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _drdob3.ToString() + "</td></tr>"

                    + "<tr><td style='width:25%' align='left'>Occupation</td>"
                    + "<td style='width: 25%' align='left'>" + _drocc3.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Driving Experience</td>"
                    + "<td style='width: 25%' align='left'>" + _drexp3.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_drcount) > 3)
                {
                    _html += "<tr><td colspan='4' align='left'>Driver 4</td></tr>"
                    + "<tr><td style='width:25%' align='left'>Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _drname4.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Date of Birth</td>"
                    + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _drdob4.ToString() + "</td></tr>"

                    + "<tr><td style='width:25%' align='left'>Occupation</td>"
                    + "<td style='width: 25%' align='left'>" + _drocc4.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Driving Experience</td>"
                    + "<td style='width: 25%' align='left'>" + _drexp4.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_drcount) > 4)
                {
                    _html += "<tr><td colspan='4' align='left'>Driver 5</td></tr>"
                    + "<tr><td style='width:25%' align='left'>Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _drname5.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Date of Birth</td>"
                    + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _drdob5.ToString() + "</td></tr>"

                    + "<tr><td style='width:25%' align='left'>Occupation</td>"
                    + "<td style='width: 25%' align='left'>" + _drocc5.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Driving Experience</td>"
                    + "<td style='width: 25%' align='left'>" + _drexp5.ToString() + "</td></tr>";
                }

                _html += "<tr><td colspan='4' align='left'>Period of insurance</td></tr>"

                + "<tr><td style='width:25%' align='left'>From</td>"
                + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _insstartdate.ToString() + "</td>"
                + "<td style='width: 25%' align='left'>To</td>"
                + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _insenddate.ToString() + "</td></tr>"

                + "<tr><td style='width:25%' align='left'>Is this vehicle under Hire Purchase?</td>"
                + "<td style='width: 25%' align='left'>" + _hpstatus.ToString() + "</td>"
                + "<td style='width: 25%' align='left'>Name of finance Company</td>"
                + "<td style='width: 25%' align='left'>" + _hpcomp.ToString() + "</td></tr>";

                if (_insstatus == "Yes" && _inscomid != "01" && _inscomid != "177")
                {
                    _html += "<tr><td style='width: 25%' align='left'>Current Insurance Company</td>"
                            + "<td colspan='3' align='left'>" + _inscomp.ToString() + "</td></tr>"

                            + "<tr><td style='width:25%' align='left'>Policy No.</td>"
                            + "<td style='width:25%' align='left'>" + _inspolicyno.ToString() + "</td>"
                            + "<td style='width: 25%' align='left'>Expiry Date</td>"
                            + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _inspldate.ToString() + "</td></tr>";
                }
                else
                {
                    _html += "<tr><td style='width: 25%' align='left'>Current Insurance Company</td>"
                            + "<td colspan='3' align='left'>" + _inscomp.ToString() + "</td></tr>";
                }
                _html += "<tr>"
                + "<td colspan='4' align='left'>Claim history o proposer & named / Authorised Driver(s)(Last 3 years) : " + _claimstatus.ToString() + "</td>"
                + "</tr>";
                if (_claimstatus.ToString() == "Yes")
                {
                    if (claimamt1.ToString() != "0" && claimamt1.ToString() != "")
                    {
                        _html += "<tr><td style='width:25%' align='left'>Claim Date</td>"
                        + "<td style='width:25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'> " + _claim1ststat.ToString() + " to " + _claim1stend.ToString() + "</td>"
                        + "<td style='width: 25%' align='left'> Claim Amount</td>"
                        + "<td style='width: 25%' align='left'>" + claimamt1.ToString() + "</td></tr>";
                    }
                    if (claimamt2.ToString() != "0" && claimamt2.ToString() != "")
                    {
                        _html += "<tr><td style='width:25%' align='left'>Claim Date</td>"
                        + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _claim2ndstat.ToString() + " to " + _claim2ndend.ToString() + "</td>"
                        + "<td style='width: 25%' align='left'> Claim Amount</td>"
                        + "<td style='width: 25%' align='left'>" + claimamt2.ToString() + "</td></tr>";
                    }
                    if (claimamt3.ToString() != "0" && claimamt3.ToString() != "")
                    {
                        _html += "<tr><td style='width:25%' align='left'>Claim Date</td>"
                        + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _claim3rdstat.ToString() + " to " + _claim3rdend.ToString() + "</td>"
                        + "<td style='width: 25%' align='left'> Claim Amount</td>"
                        + "<td style='width: 25%' align='left'>" + claimamt3.ToString() + "</td></tr>";
                    }
                }
                _html += "<tr><td colspan='4' align='left'><b>General Questions</td></tr>"

                + "<tr><td colspan='3' align='left'>Have you or your named driver(s) been given / accumulated demerit points during the last 24 months?</td>"
                + "<td style='width: 25%' align='left'>" + _demeritstatus.ToString() + "</td></tr>";
                if (Convert.ToInt16(_demeritcount) > 0)
                {
                    _html += "<tr><td style='width:25%' align='left'>1. Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrname1.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Total Demerit Points</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrpoint1.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_demeritcount) > 1)
                {
                    _html += "<tr><td style='width:25%' align='left'>2. Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrname2.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Total Demerit Points</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrpoint2.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_demeritcount) > 2)
                {
                    _html += "<tr><td style='width:25%' align='left'>3. Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrname3.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Total Demerit Points</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrpoint3.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_demeritcount) > 3)
                {
                    _html += "<tr><td style='width:25%' align='left'>4. Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrname4.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Total Demerit Points</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrpoint4.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_demeritcount) > 4)
                {
                    _html += "<tr><td style='width:25%' align='left'>5. Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrname5.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Total Demerit Points</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrpoint5.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_demeritcount) > 5)
                {
                    _html += "<tr><td style='width:25%' align='left'>5. Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrname6.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Total Demerit Points</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrpoint6.ToString() + "</td></tr>";
                }
                _html += "<tr><td colspan='3' align='left'>Have you or your named driver(s) had any motor insurance proposal declined, cancelled or renewal rejected by any insurance company?</td>"
                + "<td style='width: 25%' align='left'>" + _inscancelstatus.ToString() + "</td></tr>";
                if (_inscancelstatus.ToString() == "Yes")
                {
                    _html += "<tr><td style='width: 25%' align='left'>Reason</td>"
                    + "<td colspan='3' align='left'>" + _cancelreason.ToString() + "</td></tr>";
                }
                _html += "<tr><td colspan='3' align='left'>Have you or your named driver(s) suffered any disease or infirmity that could impair the ability to drive?</td>"
                        + "<td style='width: 25%' align='left'>" + _impairstatus.ToString() + "</td></tr>";
                if (_inscancelstatus.ToString() == "Yes")
                {
                    _html += "<tr><td style='width: 25%' align='left'>Reason</td>"
                    + "<td colspan='3' align='left'>" + _impairreason.ToString() + "</td></tr>";
                }

                _html += "<tr><td colspan='4' align='left'></td></tr>";
                _html += "<tr><td>Account No.</td><td>[" + _cxaccountno + "]</td><td>Policy No.</td><td>[" + _policyno + "]</td></tr>";
                _html += "<tr><td>Order No.</td><td>[" + _orderno + "]</td><td>AuCode</td><td>[" + _aucode + "]</td></tr>";

                _html += "</table>"
                + "</td>"
                + "</tr>"

                + "</table>"
                + "</div></div>";

                #endregion

                lblContent.Text = _html;
                MyIO myio = new MyIO();

                string[] filecontent = new string[1];
                filecontent[0] = strText + lblContent.Text.ToString();

                //_filePath = GetFilePath();
                myio.WriteNewFile(FilePath, FileName + ".xls", filecontent);
                _returnFilePath = FilePath + FileName + ".xls";
                ////SEND MAIL
                //string _providerEmail = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaCI_TO");
                //string _providerCCEmail = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaCI_CC");
                //string _providerBCCEmail = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaCI_BCC");


                ////string _providerEmail = "cherylmae.lanot@teledirect.com.sg";
                //string[] filelist = new string[1];
                ////string _excelfile = Server.MapPath("proposalforms/" + _fileName + ".xls");
                //string _excelfile = Server.MapPath("~/exceloutput/" + _fileName + ".xls");
                //filelist[0] = _excelfile;
                //string _refid = "E" + convertno;
                //MyMailManager.SendCarProposalAttachment("Etiqa Insurance", _providerEmail, _providerCCEmail, _providerBCCEmail, "", CE.Global.General.GetUTCDateTime().ToShortDateString(), CE.Global.General.GetUTCDateTime().ToShortTimeString(), _refid, "Etiqa", filelist);

            }
        }
        catch (Exception Ex)
        {
            Ex = null;
        }

        return _returnFilePath;

    }

    private string GetFilePath()
    {
        string _filePath = CE.Configuration.MyConfigurationManager.GetAppSettingValue("FileOutputPath").ToString();
        _filePath += "exceloutput\\etiqa\\";
        CE.InputOut.MyIO _myIO = new CE.InputOut.MyIO();
        string _todaydate = CE.Global.General.GetUTCDateTime().ToString("yyyyMMMdd");
        if (!_myIO.CheckDirectory(_filePath + _todaydate))
        {
            if (_myIO.CreateDirectory(_filePath, _todaydate))
            {
                _filePath += _todaydate + "\\";
            }
        }
        else
        {
            _filePath += _todaydate + "\\";
        }

        return _filePath;
    }


    public Int64 InsertEtiqaPaymentGateWayReply(string _ordernumber, string _authresp, string _authcode, string _authdt, string _cctref, string _amount, string _cemail)
    {
        MyDatabase _db = new MyDatabase();
        Int64 _result = -1;
        string _spName = "sprocEtiqPaymentGateWayReplyInsertSingleItem";

        SortedList _sPara = new SortedList();

        #region Parameter

        _sPara.Add("@OrderNo", _ordernumber);
        _sPara.Add("@AuthResp", _authresp);
        _sPara.Add("@Info1", _authcode);
        _sPara.Add("@Info2", _authdt);
        _sPara.Add("@Info3", _cctref);
        _sPara.Add("@Info4", _amount);
        _sPara.Add("@Info5", _cemail);
        #endregion

        _result = _db.ExecuteSP(_spName, _sPara);

        return _result;
    }

}
